Creates 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 ]

where column_constraint is:

  [ 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 is:

  [ 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 ]

Description

The CREATE TABLE command creates an empty table in the current database. The table is owned by the user who runs the command.

If you specify a schema name (for example, you specify CREATE TABLE myschema.mytable), the table is created in the specified schema. Otherwise, the table is created in the current schema. Temporary tables exist in a special schema. Therefore, you do not need to specify the schema name when creating a temporary table. The table name must be different from all other tables, sequences, indexes, or views in the same schema.

The CREATE TABLE command automatically creates a composite data type that corresponds to a row in the table. Therefore, a table cannot have the same name as an existing data type in the same schema.

A table can have up to 1,600 columns. In practice, the effective limit is lower because of tuple-length constraints

The optional constraint clauses specify constraints or tests that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table.

Constraints are classified into table constraints and column constraints. A column constraint is defined as part of a column definition. The table constraint definition does not depend on specific columns and can contain multiple columns. Each column constraint can also be written as a table constraint. If a constraint affects only one column, the constraint is a column constraint. This means that a column constraint is only a notational convenience.

Parameters

Parameter Description
GLOBAL TEMPORARY If this parameter is specified, the table is created as a temporary table. Temporary tables are automatically deleted at the end of a session or at the end of the current transaction. For more information, see the ON COMMIT parameter. If a temporary table exists, existing permanent tables with the same names are invisible to the current session, unless the temporary table is referenced by schema-qualified names. A temporary table is invisible outside the session in which it was created. This aspect of global temporary tables is incompatible with Oracle databases. All indexes created on a temporary table are automatically temporary.
table_name The name of the table to be created. The name can be schema-qualified.
column_name The name of a column to be created in the new table.
data_type The data type of the column. Array specifiers can be included.
DEFAULT default_expr The DEFAULT clause assigns a default data value for the column. The value is a variable-free expression. Subqueries or cross-references to other columns in the current table are not allowed. The data type of the default expression must be the same as that of the column.
Note The default expression is used in an insert operation that does not specify a value for the column. If no default value is specified for the column, the default value is null.
CONSTRAINT constraint_name An optional name for a column or table constraint. If this parameter is not specified, the system generates a name.
NOT NULL The column cannot contain null values.
NULL The column can contain null values. This is the default value.

This clause is available only for compatibility with non-standard SQL databases. We recommend that you do not use this clause in new applications.

UNIQUE: column constraint

UNIQUE (column_name [, ...] ): table constraint

The UNIQUE constraint specifies that a group of one or more distinct columns of a table can contain only unique values. The behavior of a unique table constraint is the same as that of a column constraint except the additional capability to span multiple columns.

When a unique constraint is evaluated, null values are not considered to be equal.

Each unique table constraint must name a set of columns that is different from the set of columns named by other unique or primary key constraints defined for the table. Otherwise, the same constraint is listed twice.

PRIMARY KEY: column constraint

PRIMARY KEY ( column_name [, ...] ): table constraint

The primary key constraint specifies that one or more columns of a table can contain only unique, non-duplicate, and non-null values. PRIMARY KEY is a combination of UNIQUE and NOT NULL. PRIMARY KEY identifies a set of columns as the primary key and provides metadata about the design of the schema. A primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

Only one primary key can be specified for a table, whether as a column constraint or a table constraint.

The primary key constraint must name a set of columns that is different from other sets of columns named by a unique constraint defined for the same table.

CHECK (expression) The CHECK clause specifies an expression that produces a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. If an expression is evaluated as TRUE or unknown, the INSERT or UPDATE operation is successful. If a row of an insert or update operation produces a FALSE result, an error occurs and the insert or update does not alter the database. A check constraint specified as a column constraint must reference only the value of the column. An expression in a table constraint can reference multiple columns.

CHECK expressions cannot contain subqueries or reference variables other than columns of the current row.

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

FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ]: table constraint

These clauses specify a foreign key constraint. A group of one or more columns in the new table must contain only values that match the values in the referenced columns of a row in the referenced table. If refcolumn is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table.
In addition, when data in the referenced columns is changed, actions are performed on the data in 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. Possible actions for each clause are described as follows:
  • CASCADE: deletes all rows that reference the deleted row, or updates the value of the referencing column to the new value of the referenced column.
  • SET NULL: sets the referencing columns to NULL.

If the referenced column changes frequently, you can add an index to the foreign key column to facilitate reference actions associated with the foreign key column.

DEFERRABLE

NOT DEFERRABLE

This parameter controls whether the constraint can be deferred. A constraint that is not deferrable is checked immediately after each command. Checking of deferrable constraints can be postponed until the end of the transaction by using the SET CONSTRAINTS command. NOT DEFERRABLE is the default value. Only foreign key constraints accept this clause. All other constraint types are not deferrable.
INITIALLY IMMEDIATE

INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default value. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. You can use the SET CONSTRAINTS command to change the constraint check time.
ON COMMIT You can use the ON COMMIT clause to control the behavior of temporary tables at the end of a transaction block. The following options are available:
  • PRESERVE ROWS: No special action is performed at the end of each transaction. This is the default behavior. Note that this aspect is incompatible with Oracle databases. The default value for Oracle databases is DELETE ROWS.
  • DELETE ROWS: All rows in the temporary table are deleted at the end of each transaction block. An automatic TRUNCATE command is executed at each commit operation.
TABLESPACE tablespace The tablespace is the name of the tablespace in which the new table is to be created. If you do not specify the tablespace, default_tablespace is used. If default_tablespace is an empty string, the default tablespace of the database is used.
USING INDEX TABLESPACE tablespace This clause allows you to select the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint is created. If you do not specify the tablespace, default_tablespace is used. If default_tablespace is an empty string, the default tablespace of the database is used.
Note The PolarDB-O automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. You do not need to create an explicit index for primary key columns. For more information, see the CREATE INDEX command.

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. Unique table constraints can be defined on one or more columns of 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 check column 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 check table 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 primary key table constraint for the jobhist table. Primary key table constraints 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)
);

Assign a literal constant default value for the column job and set the default value of hiredate to the date at 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 the dept table 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;