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 When this parameter is specified, the resulting table is a temporary table. Temporary tables are automatically deleted after the current session ends. You can also manually delete temporary tables after you complete transactions. For more information, see the description about the ON COMMIT parameter in this table.
Note
  • If a temporary table shares the same name with a permanent table, the permanent table will not be visible in the current session unless the permanent table is referenced by using the schema-qualified name.
  • The temporary table is visible only in the current session. This mechanism is different from the global temporary table mechanism for 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. The value can contain array specifiers.
DEFAULT default_expr The expression that is used to generate a default value for the defined column. This expression is specified in the DEFAULT clause. This expression cannot contain subqueries or references to other columns in the table. The data type of this default expression must be the same as that of the column.

When no values are inserted for a column, this default expression is used to generate a value. If you do not specify the default expression, the value used 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 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. This expression is specified in the CHECK clause.

An INSERT or UPDATE operation can succeed only when this expression evaluates to true or unknown. Otherwise, the operation fails and an error is returned. If you define a CHECK constraint on a column, you can limit the values that can be written to the column. If you define a CHECK constraint on a table, you can limit the values that can be written to multiple columns in the table.

Note This 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 specified, 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, or updates the values in the columns that reflect the values of the referenced columns.
  • SET NULL: sets the values in the referencing columns to NULL.
Note If the values of the referenced columns change frequently, 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. When you postpone the check of a constraint, the constraint is not checked until the end of the transaction.

NOT DEFERRABLE is the default value. Only foreign key constraints support the DEFERRABLE and NOT DEFERRABLE values. Other constraints are non-deferrable.

DEFERRABLE
INITIALLY IMMEDIATE The time when the specified deferrable constraint is checked.

The INITIALLY IMMEDIATE value specifies that the constraint is checked after each statement is executed. INITIALLY IMMEDIATE is the default value.

The INITIALLY DEFERRED value specifies that the constraint is checked after the transaction ends.

You can execute the SET CONSTRAINTS statement to change the time when the constraint is checked.
INITIALLY DEFERRED
ON COMMIT The action that is performed on the temporary table at the end of a transaction block. Default value: PRESERVE ROWS. Valid values:
  • PRESERVE ROWS: No special action is performed at the end of each transaction block.
    Note The default value is different from the default value of Oracle databases. The default value of 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 when a commit operation is performed.

Description

The CREATE TABLE statement creates a table in the current database. The table is initially empty. The user who 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. Temporary tables exist in a dedicated 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 may contain 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. The INSERT or UPDATE operation can succeed only if the operation meets the constraint or test condition. A constraint is an SQL object that you can use to limit the values of data that can be written to a table. PolarDB provides multiple constraint methods. You can define a constraint as a column constraint or a table constraint.
  • A column constraint is defined on a single column.
  • A table constraint is defined on multiple columns in a table.
To simplify table management, you can define a column constraint as a table constraint on only one column.
Note PolarDB automatically creates an index for each UNIQUE constraint and PRIMARY KEY constraint to enforce uniqueness. This way, you do not need to create an explicit index for each column that is included in the primary key.

Examples

Create two tables: dept and emp.

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 when 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;