The CREATE TABLE statement creates a new table in a database.

By default, tables are created in the default replication zone but can be placed into a specific replication zone. See Create a Replication Zone for a Table for more information.

Required Privileges

The user must have the CREATE privilege on the parent database.

Synopsis

CREATE TABLE IF NOT EXISTS any_name ( column_def index_def family_def table_constraint , ) opt_interleave

column_def ::=

name typename col_qual_list

col_qual_list ::=

CONSTRAINT name NOT NULL UNIQUE PRIMARY KEY CHECK ( a_expr ) DEFAULT b_expr REFERENCES qualified_name opt_name_parens COLLATE any_name FAMILY name CREATE FAMILY opt_name IF NOT EXISTS FAMILY name

index_def ::=

UNIQUE INDEX name ( index_elem , ) COVERING STORING ( name_list ) opt_interleave

family_def ::=

FAMILY name ( name , )

table_constraint ::=

CONSTRAINT name CHECK ( a_expr PRIMARY KEY ( name_list ) UNIQUE ( name_list ) COVERING STORING ( name_list ) opt_interleave FOREIGN KEY ( name_list ) REFERENCES qualified_name opt_column_list

opt_interleave ::=

INTERLEAVE IN PARENT name ( interleave_prefix )

Parameters

Parameter Description
IF NOT EXISTS Create a new table only if a table of the same name does not already exist in the database; if one does exist, do not return an error.

Note that IF NOT EXISTS checks the table name only; it does not check if an existing table has the same columns, indexes, constraints, etc., of the new table.
any_name The name of the table to create, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as database.name.

The UPSERT and INSERT ON CONFLICT statements use a temporary table called excluded to handle uniqueness conflicts during execution. It’s therefore not recommended to use the name excluded for any of your tables.
column_def A comma-separated list of column definitions. Each column requires a name/identifier and data type; optionally, a column-level constraint can be specified. Column names must be unique within the table but can have the same name as indexes or constraints.

Any PRIMARY KEY, UNIQUE, and CHECK constraints defined at the column level are moved to the table level as part of the table’s creation. Use the SHOW CREATE TABLE statement to view them at the table level.
index_def An optional, comma-separated list of index definitions. For each index, the column(s) to index must be specified; optionally, a name can be specified. Index names must be unique within the table and follow these identifier rules. See the Create a Table with Secondary Indexes example below.

The CREATE INDEX statement can be used to create an index separate from table creation.
family_def An optional, comma-separated list of column family definitions. Column family names must be unique within the table but can have the same name as columns, constraints, or indexes.

A column family is a group of columns that are stored as a single key-value pair in the underlying key-value store. CockroachDB automatically groups columns into families to ensure efficient storage and performance. However, there are cases when you may want to manually assign columns to families. For more details, see Column Families.
table_constraint An optional, comma-separated list of table-level constraints. Constraint names must be unique within the table but can have the same name as columns, column families, or indexes.
opt_interleave You can potentially optimize query performance by interleaving tables, which changes how CockroachDB stores your data.

Examples

Create a Table (No Primary Key Defined)

In CockroachDB, every table requires a PRIMARY KEY. If one is not explicitly defined, a column called rowid of the type INT is added automatically as the primary key, with the unique_rowid() function used to ensure that new rows always default to unique rowid values. The primary key is automatically indexed.

Strictly speaking, a primary key's unique index is not created; it is derived from the key(s) under which the data is stored, so it takes no additional space. However, it appears as a normal unique index when using commands like SHOW INDEX.
> CREATE TABLE logon (
    user_id INT, 
    logon_date DATE
);

> SHOW COLUMNS FROM logon;
+------------+------+-------+----------------+
|   Field    | Type | Null  |    Default     |
+------------+------+-------+----------------+
| user_id    | INT  | true  | NULL           |
| logon_date | DATE | true  | NULL           |
| rowid      | INT  | false | unique_rowid() |
+------------+------+-------+----------------+
(3 rows)
> SHOW INDEX FROM logon;
+-------+---------+--------+-----+--------+-----------+---------+
| Table |  Name   | Unique | Seq | Column | Direction | Storing |
+-------+---------+--------+-----+--------+-----------+---------+
| logon | primary | true   |   1 | rowid  | ASC       | false   |
+-------+---------+--------+-----+--------+-----------+---------+
(1 row)

Create a Table (Primary Key Defined)

In this example, we create a table with three columns. One column is the PRIMARY KEY, another is given the UNIQUE constraint, and the third has no constraints. The primary key and column with the UNIQUE constraint are automatically indexed.

By default, CockroachDB would assign the user_id and logoff_date columns to a single column family, since they’re of a fixed size, and user_email to its own column family, since it’s unbounded. We know that user_email will be relatively small, however, so we use the FAMILY keyword to group it with the other columns. As a result, each new row in the table would correspond to a single underlying key-value pair. For more deails about how columns are assigned to column families, see Column Families.

CREATE TABLE logoff (
    user_id INT PRIMARY KEY, 
    user_email STRING UNIQUE, 
    logoff_date DATE,
    FAMILY f1 (user_id, user_email, logoff_date)
);

> SHOW COLUMNS FROM logoff;
+-------------+------------+-------+---------+
|    Field    |    Type    | Null  | Default |
+-------------+------------+-------+---------+
| user_id     | INT        | false | NULL    |
| user_email  | STRING     | true  | NULL    |
| logoff_date | DATE       | true  | NULL    |
+-------------+------------+-------+---------+
(3 rows)
> SHOW INDEX FROM logoff;
+--------+-----------------------+--------+-----+------------+-----------+---------+
| Table  |         Name          | Unique | Seq |   Column   | Direction | Storing |
+--------+-----------------------+--------+-----+------------+-----------+---------+
| logoff | primary               | true   |   1 | user_id    | ASC       | false   |
| logoff | logoff_user_email_key | true   |   1 | user_email | ASC       | false   |
+--------+-----------------------+--------+-----+------------+-----------+---------+
(2 rows)

Create a Table With Secondary Indexes

In this example, we create two secondary indexes during table creation. Secondary indexes allow efficient access to data with keys other than the primary key. This example also demonstrates a number of column-level and table-level constraints.

> CREATE TABLE product_information (
    product_id           INT PRIMARY KEY NOT NULL,
    product_name         STRING(50) UNIQUE NOT NULL,
    product_description  STRING(2000),
    category_id          STRING(1) NOT NULL CHECK (category_id IN ('A','B','C')),
    weight_class         INT,
    warranty_period      INT CONSTRAINT valid_warranty CHECK (warranty_period BETWEEN 0 AND 24),
    supplier_id          INT,
    product_status       STRING(20),
    list_price           DECIMAL(8,2),
    min_price            DECIMAL(8,2),
    catalog_url          STRING(50) UNIQUE,
    date_added           DATE DEFAULT CURRENT_DATE(),
    CONSTRAINT price_check CHECK (list_price >= min_price),
    INDEX date_added_idx (date_added),
    INDEX supp_id_prod_status_idx (supplier_id, product_status)
);

> SHOW INDEX FROM product_information;
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+
|        Table        |                 Name                 | Unique | Seq |     Column     | Direction | Storing |
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+
| product_information | primary                              | true   |   1 | product_id     | ASC       | false   |
| product_information | product_information_product_name_key | true   |   1 | product_name   | ASC       | false   |
| product_information | product_information_catalog_url_key  | true   |   1 | catalog_url    | ASC       | false   |
| product_information | date_added_idx                       | false  |   1 | date_added     | ASC       | false   |
| product_information | supp_id_prod_status_idx              | false  |   1 | supplier_id    | ASC       | false   |
| product_information | supp_id_prod_status_idx              | false  |   2 | product_status | ASC       | false   |
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+
(6 rows)

We also have other resources on indexes:

Create a Table with Foreign Keys

Foreign keys guarantee a column uses only values that already exist in the column it references, which must be from another table. This constraint enforces referential integrity between the two tables.

There are a number of rules that govern foreign keys, but the two most important are:

  • Foreign key columns must be indexed when creating the table using INDEX, PRIMARY KEY, or UNIQUE.
  • Referenced columns must contain only unique values. This means the REFERENCES clause must use exactly the same columns as a PRIMARY KEY or UNIQUE constraint.

In this example, we’ll show a series of tables using different formats of foreign keys.

> CREATE TABLE customers (id INT PRIMARY KEY, email STRING UNIQUE);

> CREATE TABLE products (sku STRING PRIMARY KEY, price DECIMAL(9,2));

> CREATE TABLE orders (
  id INT PRIMARY KEY,
  product STRING NOT NULL REFERENCES products,
  quantity INT,
  customer INT NOT NULL CONSTRAINT valid_customer REFERENCES customers (id),
  CONSTRAINT id_customer_unique UNIQUE (id, customer),
  INDEX (product),
  INDEX (customer)
);

> CREATE TABLE reviews (
  id INT PRIMARY KEY,
  product STRING NOT NULL REFERENCES products,
  customer INT NOT NULL,
  "order" INT NOT NULL,
  body STRING,
  CONSTRAINT order_customer_fk FOREIGN KEY ("order", customer) REFERENCES orders (id, customer),
  INDEX (product),
  INDEX (customer),
  INDEX ("order", customer)
);

Show the Definition of a Table

To show the definition of a table, use the SHOW CREATE TABLE statement. The contents of the CreateTable column in the response is a string with embedded line breaks that, when echoed, produces formatted output.

> SHOW CREATE TABLE logoff;
+--------+----------------------------------------------------------+
| Table  |                       CreateTable                        |
+--------+----------------------------------------------------------+
| logoff | CREATE TABLE logoff (␤                                   |
|        |     user_id INT NOT NULL,␤                               |
|        |     user_email STRING(50) NULL,␤                         |
|        |     logoff_date DATE NULL,␤                              |
|        |     CONSTRAINT "primary" PRIMARY KEY (user_id),␤         |
|        |     UNIQUE INDEX logoff_user_email_key (user_email),␤    |
|        |     FAMILY "primary" (user_id, user_email, logoff_date)␤ |
|        | )                                                        |
+--------+----------------------------------------------------------+
(1 row)

See Also



Yes No