CREATE TABLE in Oracle

CREATE TABLE To create a new table in the database, Oracle provides the Oracle CREATE TABLE statement.

Syntax:

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );

Parameters:

schema_name: It is used to specify the name of the schema to which the new table belongs. table_name: It is used to specify the table name. Column Definition: Column1, column2, … column n is used to specify the name of the multiple columns which you want to add in the table. The Oracle database does not allow a total number of columns more than 32. A datatype is a must for each column. The data type of a column can be NUMBER, VARCHAR2, etc. The column constraint mainly defines each column as “NULL” or “NOT NULL”, with the value of “NULL” as default. Some other common column constraints are Primary Key, Check and Foreign Key.

Example 1: Creating a table with NULL and NOT NULL table constraint.

CREATE TABLE students  
( id number(10) NOT NULL,  
  name varchar2(40) NOT NULL,  
  class varchar2(10)  
);

Explanation: Column_1: id: It is the name of the first column. number: It is the datatype of the first column which also specifies a maximum limit of 10 digits in length for the “id”. NOT NULL: It defines the column constraint of the first column and thus Column 1 cannot contain null values. Column_2: name: It is the name of the second column. varchar: It is the datatype of the second column which also specifies a maximum limit of 40 characters in length for the “name”. NOT NULL: It defines the column constraint of the second column and thus Column 2 cannot contain null values. Column_3: class: It is the name of the third column. varchar: It is the datatype of the third column which also specifies a maximum limit of 10 characters in length for the “class”. NULL: There is no column constraint defined for the third column, thus it is NULL by default and thus Column 3 can contain null values.

Example 2: Creating a table with a PRIMARY KEY table constraint.

CREATE TABLE students  
( id number(10) NOT NULL,  
  name varchar2(40) NOT NULL,  
  class varchar2(10)  
  PRIMARY KEY(id)
);

Explanation: All the three columns and their definitions are the same as that in example 1. The difference is in the existence of Primary Key in the second example. The PRIMARY KEY clause is a field or combination of fields which specifies a column as the primary key column. The primary key column is used for distinguishing a unique row in a table. In Oracle, a table can hold only one primary key, and every field of the primary key must contain NOT NULL values. In the above example, “id” is defined as the Primary Key Column.

Please follow and like us: