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 ]

The column_constraint parameter uses the following syntax:

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

The table_constraint parameter uses the following syntax:

  [ 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 issued 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 a schema name when you create a temporary table. The table must have a different name than all other tables, sequences, indexes, or views in the same schema.

The CREATE TABLE command also automatically creates a composite data type that is associated with one 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 Specifies to create a temporary table. The difference between temporary tables and standard tables lies in data management. Temporary tables store intermediate result sets of transactions and sessions. In a temporary table, the stored data is visible only to the session in which the table was created. The data is not visible to other sessions even though other sessions have been committed. You do not need to concern about concurrency issues of temporary tables because temporary tables are isolated by sessions. Temporary tables are divided into transaction-level temporary tables and session-level temporary tables. For more information, see the ON COMMIT parameter.
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 to 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 match the data type 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 for 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. This set of columns is different from the set of columns that is 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. This set of columns must be different from other sets of columns that are named by a unique constraint defined for the same table.

CHECK (expression) The CHECK clause specifies an expression for INSERT or UPDATE operations to succeed. This expression produces a Boolean result that new or updated rows must satisfy. 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 operation 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.
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 while a referenced row in the referenced table is being deleted. Referential actions cannot be deferred even if the constraint is deferrable. The following actions are possible actions for each clause:
  • 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. This facilitates reference actions that are 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. You can run the SET CONSTRAINTS command to defer the checking of deferrable constraints until the end of the transaction. 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 run 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: A session-level temporary table retains data until the connection is closed or the DELETE or TRUNCATE command is run. Data in the temporary table is visible only to the current session. Note that this is incompatible with Oracle databases. The default value for Oracle databases is DELETE ROWS.
  • DELETE ROWS: A transaction-level temporary table retains data until the COMMIT or ROLLBACK command is run. The data is automatically deleted after the transaction is committed. The TRUNCATE command is automatically run at each commit operation.
Note PolarDB O Edition automatically creates indexes for each unique constraint and primary key constraint to enforce uniqueness. You do not need to create explicit indexes 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. You can define unique table constraints 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. You can define primary key table constraints 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 to the job column and set the default value of hiredate to the date on which the row was 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;