Understand CREATE TABLE Syntax & Constraints in PolarDB Oracle - PolarDB

Creates an empty table in the current database.

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 ]

Column constraint 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 ]

Table constraint 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

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

To place the table in a specific schema, use the form CREATE TABLE myschema.mytable .... Otherwise, the table is created in the current schema. Temporary tables exist in a special schema, so no schema name is needed when creating them.

The table name must be unique within its schema — no other table, sequence, index, or view in the same schema can share the name. CREATE TABLE also automatically creates a composite data type representing one row in the table, so the table name must not conflict with any existing data type name in the same schema.

A table can have up to 1,600 columns. The effective limit is often lower due to tuple-length constraints.

Constraints specify conditions that rows must satisfy for INSERT or UPDATE operations to succeed. A column constraint is defined as part of the column definition. A table constraint is defined independently and can span multiple columns. Each column constraint can also be written as a table constraint; use whichever is more readable.

Note automatically creates indexes for UNIQUE and PRIMARY KEY constraints. You do not need to create explicit indexes for primary key columns. For details, see the CREATE INDEX command.

Parameters

Parameter	Description
`GLOBAL TEMPORARY`	Creates a temporary table. Data is visible only to the session that created the table — other sessions cannot see it, even after they commit. Temporary tables are isolated by session, so no concurrent access conflicts occur. Two lifetime modes are available: session-level and transaction-level. See `ON COMMIT` for details.
`table_name`	The name of the table. Can be schema-qualified.
`column_name`	The name of a column.
`data_type`	The data type of the column. Array specifiers are allowed.
`DEFAULT default_expr`	Assigns a default value to the column. The expression must be variable-free — subqueries and cross-references to other columns in the same table are not allowed. The expression's data type must match the column's data type. When an INSERT omits a value for the column, the default value is used. If no default is specified, the default is NULL.
`TABLESPACE tablespace`	Specifies the tablespace. Custom tablespaces are not supported.
`CONSTRAINT constraint_name`	An optional name for a column or table constraint. If omitted, the system generates a name.
`NOT NULL`	Rejects NULL values in the column.
`NULL`	Allows NULL values in the column. This is the default. This clause exists only for compatibility with non-standard SQL databases; avoid it in new code.
`UNIQUE` (column constraint) `UNIQUE (column_name [, ...])` (table constraint)	Requires that all values in the specified column or column group be unique. NULL values are not considered equal when evaluating uniqueness. Each UNIQUE table constraint must name a distinct set of columns — duplicating another UNIQUE or PRIMARY KEY constraint's column set is an error. Note PolarDB automatically creates an index for each UNIQUE constraint; no explicit index is needed.
`PRIMARY KEY` (column constraint) `PRIMARY KEY (column_name [, ...])` (table constraint)	Combines NOT NULL and UNIQUE: values must be non-null and unique. Only one PRIMARY KEY can be defined per table. The column set named by PRIMARY KEY must differ from the column sets named by any UNIQUE constraints on the same table. Note PolarDB automatically creates an index for PRIMARY KEY; no explicit index is needed.
`CHECK (expression)`	Specifies a Boolean condition that each inserted or updated row must satisfy. If the expression evaluates to TRUE or unknown, the operation succeeds. If it evaluates to FALSE, the INSERT or UPDATE is rejected. A column-level CHECK can reference only that column's value; a table-level CHECK can reference multiple columns. CHECK expressions cannot contain subqueries or reference anything outside the current row's columns.
`REFERENCES reftable [(refcolumn)] [ON DELETE action]` (column constraint) `FOREIGN KEY (column [, ...]) REFERENCES reftable [(refcolumn [, ...])] [ON DELETE action]` (table constraint)	Defines a foreign key constraint: values in the specified column or columns must match a value in the referenced table. If `refcolumn` is omitted, the referenced table's primary key is used. The referenced columns must be covered by a UNIQUE or PRIMARY KEY constraint. The `ON DELETE` clause controls what happens when a referenced row is deleted. Reference actions cannot be deferred even when the constraint is declared DEFERRABLE. Available actions: `CASCADE` — deletes all referencing rows, or updates the referencing column to the new value of the referenced column; `SET NULL` — sets the referencing column to NULL. For frequently updated referenced columns, add an index on the foreign key column to improve performance.
`DEFERRABLE` / `NOT DEFERRABLE`	Controls whether constraint checking can be postponed. A NOT DEFERRABLE constraint is checked immediately after each statement. A DEFERRABLE constraint can be postponed to the end of the transaction using `SET CONSTRAINTS`. NOT DEFERRABLE is the default. Only foreign key constraints support this option — all other constraint types are always immediate.
`INITIALLY IMMEDIATE` / `INITIALLY DEFERRED`	Sets the default check timing for a deferrable constraint. INITIALLY IMMEDIATE (the default) checks the constraint after each statement. INITIALLY DEFERRED checks it only at the end of the transaction. Use `SET CONSTRAINTS` to change the timing within a transaction.
`ON COMMIT`	Controls the lifetime of a temporary table's data at the end of a transaction block. Two options are available: `PRESERVE ROWS` — creates a session-level temporary table. Data persists until the session closes or you run DELETE or TRUNCATE. Data is visible only to the current session. Note This is incompatible with Oracle databases, where the default is DELETE ROWS. `DELETE ROWS` — creates a transaction-level temporary table. Data is deleted automatically when the transaction commits (TRUNCATE runs at each commit). Data persists until COMMIT or ROLLBACK.

Oracle compatibility notes

PolarDB for PostgreSQL (Compatible with Oracle) closely follows Oracle's CREATE TABLE syntax, but the following behaviors differ:

Behavior	PolarDB	Oracle
`ON COMMIT` default	`PRESERVE ROWS` (session-level)	`DELETE ROWS` (transaction-level)
Custom tablespaces	Not supported	Supported
Automatic index creation for PRIMARY KEY and UNIQUE	Yes — no need to create explicit indexes	Yes
`NULL` constraint clause	Accepted for compatibility; not recommended in new code	Supported

Examples

Create tables with a foreign key relationship

The following example creates a dept table and an emp table. The emp.deptno column references dept.deptno with a foreign key constraint.

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

Create a table with a unique constraint

The following example adds a UNIQUE constraint to the dname column of the dept table, preventing duplicate department names.

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

Create a table with a column-level check constraint

The following example ensures that every salary value inserted into emp is greater than zero.

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

Create a table with a table-level check constraint

The following example enforces two conditions on the emp table using a single table-level CHECK constraint: the employee name must not be NULL and the employee number must be greater than 7000.

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

Create a table with a composite primary key

The following example creates the jobhist table with a composite primary key spanning empno and startdate. This ensures that each employee can have at most one job record per start date.

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

Create a table with default column values

The following example sets a literal default for job and a dynamic default for hiredate. When a row is inserted without specifying these columns, job defaults to 'SALESMAN' and hiredate defaults to the current date.

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

The following example creates 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;

What's next

CREATE INDEX — create indexes on table columns
ALTER TABLE — add or modify columns and constraints
DROP TABLE — remove a table from the database