Create table statement syntax parameters and constraints - PolarDB

CREATE TABLE creates a new, empty table in the current database. The user who runs the statement becomes the table owner. After creating a table, use INSERT to add rows and ALTER TABLE to modify its structure or add constraints.

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 one of:

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

And table_constraint is one of:

[ 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`	Creates a temporary table that is automatically dropped at the end of the current session. Indexes created on a temporary table are also temporary. If a temporary table shares the same name as a permanent table, the permanent table is not visible in the current session unless referenced by its schema-qualified name.
`table_name`	The name of the table. Use the schema-qualified name to reference the table when needed.
`column_name`	The name of a column in the table.
`data_type`	The data type of the column. The value can include array specifiers.
`DEFAULT default_expr`	The default value expression for the column. The expression cannot contain subqueries or references to other columns in the table, and its data type must match the column's data type. If no value is inserted and no default is defined, `NULL` is used.
`CONSTRAINT constraint_name`	An optional name for the column or table constraint. If omitted, the system generates a name automatically.
`NOT NULL`	Prevents the column from containing `NULL` values.
`PRIMARY KEY` (column constraint)	Requires the column's values to uniquely identify each row and to be non-null. A table can have only one primary key. Columns in the primary key cannot also appear in a `UNIQUE` constraint. PolarDB automatically creates an index to enforce the primary key.
`PRIMARY KEY ( column_name [, ...] )` (table constraint)	Defines a composite primary key across multiple columns.
`CHECK (expression)`	A Boolean expression that must evaluate to `true` or unknown for an `INSERT` or `UPDATE` to succeed. The expression cannot contain subqueries or references to variables outside the current row.
`REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ]` (column constraint)	Defines a foreign key constraint. If `refcolumn` is omitted, the primary key of `reftable` is used. The referenced columns must belong to a `UNIQUE` or `PRIMARY KEY` constraint of the referenced table. If the referenced columns change frequently, add an index on the foreign key column to keep referential actions efficient.
`FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ]` (table constraint)	Defines a foreign key constraint across multiple columns.
`ON DELETE action`	The action to take when a referenced row is deleted. Referential actions cannot be deferred even if the constraint is deferrable. Supported actions: `CASCADE` (delete or update rows that reference the deleted row) and `SET NULL` (set referencing column values to `NULL`).
`DEFERRABLE` / `NOT DEFERRABLE`	Whether the constraint check can be deferred to the end of a transaction. Use `SET CONSTRAINTS` to defer a deferrable constraint. Only foreign key constraints support this option; all other constraints are non-deferrable. Default: `NOT DEFERRABLE`.
`INITIALLY IMMEDIATE` / `INITIALLY DEFERRED`	When a deferrable constraint is checked. `INITIALLY IMMEDIATE` checks the constraint after each statement (default). `INITIALLY DEFERRED` checks it at the end of the transaction. Use `SET CONSTRAINTS` to override this at runtime.
`ON COMMIT`	The action to take on a temporary table at the end of a transaction block. `PRESERVE ROWS` (default) retains all rows. `DELETE ROWS` deletes all rows, equivalent to running `TRUNCATE` on each commit.
`TABLESPACE tablespace`	The tablespace in which to create the table.

Usage notes

If a schema name is included (for example, CREATE TABLE myschema.mytable), the table is created in that schema; otherwise, it is created in the current schema.
Temporary tables always exist in a dedicated schema; do not specify a schema when creating one.
The table name must be unique within the schema — it cannot duplicate the name of any existing table, sequence, index, or view.
CREATE TABLE also creates a composite data type representing a row of the table. The table name cannot match the name of an existing data type in the same schema.
A table can have up to 1,600 columns. In practice, the effective limit may be lower due to tuple length constraints.
A column constraint applies to a single column. A table constraint applies across multiple columns. For management convenience, a single-column constraint can be written as either form.
PolarDB automatically creates an index for each UNIQUE and PRIMARY KEY constraint; no explicit index creation is needed.

Compatibility notes

PolarDB for Oracle's CREATE TABLE behaves differently from Oracle databases in the following ways:

Temporary table visibility: Temporary tables are visible only in the current session. This mechanism is different from the global temporary table mechanism for Oracle databases.
ON COMMIT default: The default behavior is PRESERVE ROWS. In Oracle, the default is DELETE ROWS.

Examples

Create two related tables

The following example creates a dept table and an emp table with a foreign key referencing dept.

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

Add a UNIQUE constraint

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

Add 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)
);

Add 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 composite primary key

The following example creates a jobhist table with a composite primary key on empno and startdate.

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

Set default column values

The job column defaults to 'SALESMAN'. The hiredate column defaults to the date 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 in a specific tablespace

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

What's next

To modify an existing table's structure or constraints, use ALTER TABLE.
To remove a table, use DROP TABLE.
To add indexes beyond those created automatically for primary keys and unique constraints, use CREATE INDEX.
To populate the table with data, use INSERT.