This topic describes how to create a table.

Syntax

CREATE [ GLOBAL TEMPORARY ] TABLE table_name (
  { column_name data_type [ DEFAULT default_expr ]
  [ column_constraint [ ... ] ] | table_constraint } [, ...]
  )
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
  [ TABLESPACE tablespace ]
In the preceding syntax, column_constraint can be set to one of the following constraints:
  [ CONSTRAINT constraint_name ]
  { NOT NULL |
    NULL |
    UNIQUE [ USING INDEX TABLESPACE tablespace ] |
    PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
    CHECK (expression) |
    REFERENCES reftable [ ( refcolumn ) ]
      [ ON DELETE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |
    INITIALLY IMMEDIATE ]
table_constraint can be set to one of the following constraints:
  [ CONSTRAINT constraint_name ]
  { UNIQUE ( column_name [, ...] )
      [ USING INDEX TABLESPACE tablespace ] |
    PRIMARY KEY ( column_name [, ...] )
      [ USING INDEX TABLESPACE tablespace ] |
    CHECK ( expression ) |
    FOREIGN KEY ( column_name [, ...] )
        REFERENCES reftable [ ( refcolumn [, ...] ) ]
      [ ON DELETE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ]
  [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]        

Parameters

Parameter Description
GLOBAL TEMPORARY Specifies that the table to create is a temporary table. The temporary table is automatically deleted after the session ends. You can manually delete the temporary table after the transaction ends. For more information, see the description about the ON COMMIT parameter in this table. If the name of the temporary table is the same as the name of a permanent table, the permanent table is invisible to the session, unless the permanent table is referenced by using the schema-qualified name. The temporary table is visible only to the current session. This is different from global temporary tables in Oracle databases. Indexes that are created on the temporary table are temporary indexes.
table name The name of the table.
Note To reference this table, you can use the schema-qualified name.
column name The name of a column that you want to create in the table.
data type The data type of the column that you want to create. The value can contain array specifiers.
DEFAULT default_expr The expression that is used to generate a default value for the defined column. The expression is specified in the DEFAULT clause. The expression cannot contain subqueries or references to other columns in the table. The data type of the expression must match the data type of the column.

The expression is used in each insert operation that has no value specified for the column. If you do not use the DEFAULT clause to specify an expression, the default value is null.

CONSTRAINT constraint_name The name of the column or table constraint that you want to configure. The constraint_name parameter is optional. If you leave this parameter empty, the system automatically generates a name for the column or table constraint.
NOT NULL Specifies that the column cannot contain null values.
PRIMARY KEY - column constraint

Specifies that one or more columns must contain values that uniquely identify each row in the table and cannot contain null values. The PRIMARY KEY constraint is a combination of the UNIQUE and NOT NULL constraints. However, if the primary key consists of a set of columns, you must provide the metadata of the design of the schema. This is because the primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

Note
  • Each table can have only one primary key, regardless of whether the primary key is used as a column constraint or a table constraint.
  • The columns listed in the PRIMARY KEY constraint must not be used in other UNIQUE constraints of the table.
PRIMARY KEY ( column_name [, ...] ) - table constraint
CHECK (expression)

The expression that is used to produce a Boolean result. The expression is specified in the CHECK clause. An insert or update operation can succeed only when the calculation result produced by the expression is true or unknown. Otherwise, the operation fails and an error is returned. A CHECK constraint that is specified as a column constraint can reference only the values in the specified column. A CHECK constraint that is specified as a table constraint can reference values in multiple columns of the table.

The expression cannot contain subqueries or references to variables other than the columns of the current row.

REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ] - column constraint

These clauses specify a foreign key constraint. The foreign key constraint requires that the values in the columns of the created table must exist in the columns of the referenced table. If the refcolumn parameter is not configured, the primary key of the table that is specified by the reftable parameter is used. The referenced columns must be the columns of a UNIQUE constraint or the PRIMARY KEY constraint of the referenced table.

If the data in the referenced columns is changed, the same change operation is performed on the columns of this table. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Referential actions cannot be deferred, even if the constraint is deferrable. The ON DELETE clause supports the following referential actions:
  • CASCADE: deletes the rows that reference the deleted row.
  • SET NULL: sets the values in the referencing columns to null.

If the referenced columns are frequently changed, we recommend that you add an index to the foreign key column. This way, the referential actions associated with the foreign key column can be performed in an efficient manner.

FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ] - table constraint
DEFERRABLE NOT Specifies whether the constraint is deferrable. A non-deferrable constraint is checked immediately after each statement is executed. You can use the SET CONSTRAINTS statement to postpone the check of deferrable constraints. After you postpone the check of a constraint, the constraint is not checked until the end of the transaction. By default, the NO DEFERRABLE option is used. Only foreign key constraints support the DEFERRABLE and NO DEFERRABLE options. Other constraints are non-deferrable.
DEFERRABLE
INITIALLY IMMEDIATE The time when the specified constraint is checked. The specified constraint must be deferrable. The INITIALLY IMMEDIATE option specifies that the constraint is checked after each statement is executed. By default, the INITIALLY IMMEDIATE option is used. The INITIALLY DEFERRED option specifies that the constraint is checked after the transaction ends. You can execute the SET CONSTRAINTS statement to change the time at which the constraint is checked.
INITIALLY DEFERRED
ON COMMIT The action that is performed on the temporary table at the end of a transaction block. The following two options are available:
  • PRESERVE ROWS: No special action is performed at the end of each transaction block. PRESERVE ROWS is the default option. Note that the default option in Oracle databases is DELETE ROWS.
  • DELETE ROWS: All rows in the temporary table are deleted at the end of each transaction block. Essentially, a TRUNCATE operation is automatically performed each time a commit operation is performed.

Description

The CREATE TABLE statement creates a table in the current database. The table is initially empty. The user that executes the statement is the table owner.

If you specify a schema name in the statement, the table is created in the specified schema. Otherwise, the table is created in the current schema. For example, if you execute the CREATE TABLE myschema.mytable statement, the mytable table is created in the myschema schema. If the table that you want to create is a temporary table, you do not need to specify a schema name. This is because all temporary tables exist in a specific schema. The table name must be different from the names of other tables, sequences, indexes, and views in the same schema.

The CREATE TABLE statement also automatically creates a data type. The data type represents the composite type that corresponds to a row of the table. The table name cannot be the same as the name of an existing data type in the same schema.

A table can contain up to 1,600 columns. In practice, a table contains fewer than 1,600 columns due to constraints on the length of tuples.

An optional constraint specifies the constraint or test condition for insert or update operations. An insert or update operation can succeed only if the operation meets the constraint or test condition. A constraint is an SQL object that helps you define the set of valid values in the table in different methods. You can define a constraint as a column constraint or a table constraint.
  • A column constraint is defined as part of a column definition.
  • A table constraint affects multiple columns instead of a specific column.
For your convenience, you can define a column constraint as a table constraint that affects only one column.
Note PolarDB for Oracle automatically creates an index for each UNIQUE constraint and PRIMARY KEY constraint to enforce the uniqueness. This way, you do not need to create an explicit index for each column that is included in the primary key.

Examples

Create the dept table and the emp table.

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);
CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

Define a unique table constraint for the dept table. You can define unique table constraints for multiple columns in the table.

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc             VARCHAR2(13)
);            

Define a column-level CHECK constraint:

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);            

Define a table-level CHECK constraint:

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno),
    CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);            

Define a table-level PRIMARY KEY constraint for the jobhist table. A table-level PRIMARY KEY constraint can be defined on one or more columns of the table.

CREATE TABLE jobhist (
    empno           NUMBER(4) NOT NULL,
    startdate       DATE NOT NULL,
    enddate         DATE,
    job             VARCHAR2(9),
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2),
    chgdesc         VARCHAR2(80),
    CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate)
);            

Configure a literal constant as the default value for the job column and set the default value of the hiredate column to the date in which the row is inserted.

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9) DEFAULT 'SALESMAN',
    mgr             NUMBER(4),
    hiredate        DATE DEFAULT SYSDATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);        

Create a table named dept in the diskvol1 tablespace:

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
) TABLESPACE diskvol1;