Oracle CREATE TABLE

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
 );Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the table name and schema name to which the new table belongs on the CREATE TABLE clause.
• 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., NUMBER, 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.

Oracle CREATE TABLE statement example

The following example shows how to create a new table named persons in the ot schema:

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)
);Code language: SQL (Structured Query Language) (sql)

In this example, the  persons table has three columns: person_id, first_name, and last_name.

The 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.

The first_name column has data type VARCHAR2 with a 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 from having NULL values.

The last_name column has the same characteristics as the first_name column.

The PRIMARY KEY clause specifies the person_id column as the primary key column which is used for identifying the unique row in the  persons table.

In this tutorial, you have learned how to use the Oracle CREATE TABLE statement to create a new table.