CREATE TABLE

Create a table. The first column is always the primary key, and columns are NOT NULL unless declared otherwise.

Creates a table. Every table is stored as a B+ tree ordered by its primary key, which is always the first column.

Syntax

CREATE TABLE [<namespace>.]<table> (
    <column> <type> [NULL | NOT NULL] [REFERENCES <parent_table>] [CABIN | CABIN AUTO | NO CABIN]
    [, ...]
) [BTREE]

Arguments

Argument	Description
`<namespace>.`	Optional. Creates the table in an existing namespace. Without it, the table goes into `public`.
`<table>`	Table name, unique across the whole instance.
`<column> <type>`	A column and its data type. At least one column is required. The first column is the primary key and must be an integer type.
`NULL` / `NOT NULL`	Whether the column accepts `NULL`. The default is `NOT NULL`.
`REFERENCES <parent_table>`	Declares a foreign key to the parent table’s primary key. No parent column is written.
`CABIN`, `CABIN AUTO`, `NO CABIN`	Per-column policy for Cabins. Not allowed on the primary key.
`BTREE`	Optional; it is the default and the only storage form for new tables.

The column suffixes must appear in the order shown: nullability, then REFERENCES, then the Cabin policy.

Examples

A simple table

CREATE TABLE lounges (id int64, airport char(3), name varchar, capacity int64);

CREATED oid=4000

A table with a foreign key and a nullable column

CREATE TABLE crew (
  id         int64,
  flight_id  int64 REFERENCES flights,
  name       varchar,
  role       varchar,
  hired      date,
  note       varchar NULL
);

A table in a namespace

CREATE NAMESPACE ops;
CREATE TABLE ops.gates (id int64, flight_id int64, gate char(3));

The primary key

The first column is the row’s primary key, and it is the row’s identity rather than an ordinary field:

Issued or named. If an INSERT leaves the key out, the engine issues one. If it supplies one, that value is the key. Both work on every table, and one statement can mix them. See INSERT.
Range. Keys are integers from 1 to 2⁴⁸ − 1 (281,474,976,710,655).
Bound once. A key whose row was committed stays taken, even after DELETE, until PURGE frees it. Issued keys are never reused.
Never updated. An UPDATE that assigns the primary key is refused.
Free ordering. Rows are kept in key order whatever order keys arrive in, so ORDER BY <primary key> costs nothing.

There is no SERIAL, AUTO_INCREMENT or key-mode clause: every table already behaves this way. EXPLICIT is accepted for compatibility and does nothing.

NULL and NOT NULL

Columns are NOT NULL by default, the opposite of standard SQL. Write NULL after the type to allow nulls.

The primary key, index key columns and covered columns cannot be nullable.
A stored NULL is different from 0 and from ''. It is found by IS NULL, never by = NULL.
Nullable columns cost one byte per eight nullable columns in each row; a table with none pays nothing.

Foreign keys

REFERENCES <parent_table> makes the column point at a row of the parent table by primary key.

INSERT and UPDATE check that the parent row exists. DELETE of a parent checks that no live child row refers to it.

A violation is refused with FK_VIOLATION:

kdbs> INSERT INTO tickets VALUES (10, 9, 1, '99Z', 0, 0, 'x')
                                      ^
ERR FK_VIOLATION retryable=0 'flight_id' references row id=9 of 'flights', which does not exist (byte 32)

If the parent row is being written by another transaction, the check waits for that transaction to finish instead of failing.
Only RESTRICT behavior exists. ON DELETE CASCADE and SET NULL are not available.
SHOW FKEYS lists the declared foreign keys.

Remarks

CREATE TABLE is transactional: inside BEGIN ... COMMIT, other sessions cannot see the table until the commit, and ROLLBACK removes it.
While one transaction holds an uncommitted CREATE TABLE, another transaction cannot create the same name.
CREATE TABLE runs in a real transaction and its catalog writes go to the write-ahead log, so a committed table survives a crash and one that had not committed is rolled back when the server restarts. SHOW META reports ddl_durable=1 catalog_recovered=1.

Limitations

HEAP storage is suspended. Writing HEAP is refused; existing heap tables still work.
Columns cannot be added, dropped or retyped later. See ALTER TABLE.
IF NOT EXISTS is not supported.