Create Table in Oracle

Introduction to Create Table in Oracle

Table is an object of the database, which is used to store the entity related information like employees, department, etc, which is used to create a table in an oracle database the CREATE TABLE statement is used to create a table we need to give a name to the table and define its columns and data type for each column.

Syntax to Create Table in Oracle:

CREATE TABLE tablename
(
column1 data_type [  NOT NULL | NULL  ] ,
column2 data_type [  NOT NULL | NULL ] ,
. . .
columnn data_type [ NOT NULL | NULL ] );

Parameters:

• tablename: It specifies the table name which we want to create.
• column1, column2, . . . column: It specifies the names of the columns which we want to include in a table.
• Data_type: It specifies the data type of the column or which type of data can be stored in a column.
• “NOT NULL” or “NULL”: It specifies the constraint, the NOT NULL constraint specifies that the value cannot be null, whereas NULL specifies the value can be null or bank. The NULL constraint is the default constraint.

Example to CREATE a TABLE in an Oracle

Here we will create a table named employees, which doesn’t have any primary key.

Query:

CREATE TABLE employees
( employee_id number(15) NOT NULL ,
employee_name varchar2(20) NOT NULL ,
city varchar2(20)
);

Output:

Query:

Describe employees;

Output:

Query:

Select * from employees;

Output:

Here we are creating a table of employees and adding the columns as employee_id (It is the first column to store the employee’s id as a number data type and constraint as not null values), employee_name ( it is the second column to store employee’s name as a varchar2 data type ( size maximum 20 characters in length) and constraint as not null values and lastly city ( it is the third column to store the employee’s residing city as a varchar2 data type ( size maximum 20 characters in length and constraint as null values).

Example to CREATE a TABLE in an Oracle with a Primary Key

• query to create a primary key at the column level

Query:

CREATE TABLE employees
( employee_id number CONSTRAINT employees_pk PRIMARY KEY,
employee_name varchar(20) NOT NULL ,
city varchar(20)
) ;

Output:

Here we are creating a table employee and adding the columns as employee_id, employee_name and city to store employee’s residing city where the employee_id is created as the primary key column at the column level which means the employee_id of each employee should be unique, employee_id of any employee can not be null. In general, the primary key is used to identify each record uniquely, even the primary key can be referred to in the other table as the foreign key to store the particular entity information in another table.

Query:

Describe employees;

Output:

• query to create a primary key at table level

Query:

CREATE TABLE employees
( employee_id number NOT NULL ,
employee_name varchar(20) NOT NULL ,
city varchar(20) ,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
) ;

Output:

Here we are creating a table employee and adding the columns as employee_id, employee_name, and city to store employees residing city where the employee_id is created as the primary key column at the table level.

Query:

Describe employees;

Output:

Here we are creating a table with three columns where the first column creating as a primary key. The primary key is a single column or combination of columns that contain a unique record. The rules of a primary key are it must be filled by unique value. A null value is not allowed in a primary key column. A table can have only one primary key.

Example to CREATE a TABLE in an Oracle with Date Data Type

Here we are creating an Oracle table called employees that stores employee ID, employee name, employee address-related information, and employee hire date.

• The Oracle CREATE TABLE statement for the Employees table is:

Query:

CREATE TABLE employees
(employee_id number(10) NOT NULL ,
employee_name varchar(20) NOT NULL ,
address varchar(20) ,
city varchar(20) ,
state varchar(20) ,
zip_code varchar(20) ,
hire_date date
);

Output:

Query:

Describe employees;

Output:

Example to CREATE a TABLE in an Oracle with Primary Key and Foreign Key

Based on the employee’s table we would like to create an Oracle table called departments that stores department id, department name, and employee id. The primary key for the Employees table should be the employee id. Create a foreign key on the department’s table that references the employee’s table based on the employee_id column. The foreign key is the key that refers to another table column. The foreign key value should be from the reference primary key only, the foreign key can have duplicate values but not the null value as the primary does not have the null value.

• Oracle CREATE TABLE statement for the Employees table is:

Query:

CREATE TABLE employees
( employee_id number NOT NULL ,
employee_name varchar(20) NOT NULL ,
department_id number ,
salary number(6) ,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

Output:

Query:

Desc employees;

Output:

Query:

CREATE TABLE departments
( department_id number(10) NOT NULL ,
department_name varchar(20) NOT NULL ,
employee_id number NOT NULL ,
CONSTRAINT departments_pk PRIMARY KEY (department_id),
CONSTRAINT employees_fk
FOREIGN KEY (department_id)
REFERENCES employees (employee_id)
);

Output:

Query:

Desc departments;

Output:

Conclusion

• A table is an object of the database.
• It is used to store entity-related information like employees, departments, etc.
• In an Oracle database, the CREATE TABLE statement is used to create a table.
• The primary key is used to identify each record uniquely.

Recommended Articles

This is a guide to Create Table in Oracle. Here we discuss the introduction and parameters with examples to create a table in oracle using different keys and data types. You may also look at the following articles to learn more-

1. Top 10 Features of Oracle String Functions
2. Implementation of Oracle PLM
3. How to use Distinct Keyword in SQL?
4. Hashing in DBMS