Summary: in this tutorial, you will learn how to use the Oracle
CREATE TABLE statement to create a new table in the Oracle database.
Introduction to Oracle
CREATE TABLE statement
To create a new table in Oracle Database, you use the
CREATE TABLE statement. The following illustrates the basic syntax of the
CREATE TABLE statement:
CREATE TABLE schema_name.table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, ... table_constraint );
In this syntax:
- First, specify the table name and schema name to which the new table belongs on the
- Second, list all columns of the table within the parentheses. In case a table has multiple columns, you need to separate them by commas (,). A column definition includes the column name followed by its data type e.g.,
VARCHAR2, and a column constraint such as
NOT NULL, primary key, check.
- Third, add table constraints if applicable e.g., primary key, foreign key, check.
Note that you must have the
CREATE TABLE system privilege to create a new table in your schema and
CREATE ANY TABLE system privilege to create a new table in another user’s schema. On top of this, the owner of the new table must have the quota for the tablespace that contains the new table or
UNLIMITED TABLESPACE system privilege.
CREATE TABLE statement example
The following example shows how to create a new table named
persons in the
CREATE TABLE ot.persons( person_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(person_id) );
In this example, the
persons table has three columns:
person_id is the identity column that identifies unique rows in the table. The data type of the
person_id column is
NUMBER. The clause
GENERATED BY DEFAULT AS IDENTITYinstructs Oracle to generate a new integer for the column whenever a new row is inserted into the table.
first_name column has data type
VARCHAR2 with the maximum length is 50. It means that you cannot insert a first name whose length is greater than 50 into the
first_name column. Besides, the
NOT NULL column constraint prevents the
first_name column to have NULL values.
last_name column has the same characteristics as the
PRIMARY KEY clause specifies the
person_id column as the primary key column which is used for identifying the unique row in the
In this tutorial, you have learned how to use the Oracle
CREATE TABLE statement to create a new table.