CREATE TABLE

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

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

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

Synopsis

column_def ::=

col_qualification ::=

index_def ::=

family_def ::=

table_constraint ::=

opt_interleave ::=

Tip:
To create a table from the results of a SELECT statement, use CREATE TABLE AS.

Parameters

Table-level replication

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.

Row-level replication

CockroachDB allows enterprise users to define table partitions, thus providing row-level control of how and where the data is stored. See Create a Replication Zone for a Table Partition for more information.

Note:
The primary key required for partitioning is different from the conventional primary key. To define the primary key for partitioning, prefix the unique identifier(s) in the primary key with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions. See Partition using Primary Key for more details.

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.

Note:
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;

+-------------+-----------+-------------+----------------+-----------------------+---------+
| column_name | data_type | is_nullable | column_default | generation_expression | indices |
+-------------+-----------+-------------+----------------+-----------------------+---------+
| user_id     | INT       |    true     | NULL           |                       | {}      |
| logon_date  | DATE      |    true     | NULL           |                       | {}      |
+-------------+-----------+-------------+----------------+-----------------------+---------+
(2 rows)

> SHOW INDEX FROM logon;

+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| logon      | primary    |   false    |            1 | rowid       | ASC       |  false  |  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.

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

> SHOW COLUMNS FROM logoff;

+-------------+-----------+-------------+----------------+-----------------------+-------------------------------------+
| column_name | data_type | is_nullable | column_default | generation_expression |               indices               |
+-------------+-----------+-------------+----------------+-----------------------+-------------------------------------+
| user_id     | INT       |    false    | NULL           |                       | {"primary","logoff_user_email_key"} |
| user_email  | STRING    |    true     | NULL           |                       | {"logoff_user_email_key"}           |
| logoff_date | DATE      |    true     | NULL           |                       | {}                                  |
+-------------+-----------+-------------+----------------+-----------------------+-------------------------------------+
(3 rows)

> SHOW INDEX FROM logoff;

+------------+-----------------------+------------+--------------+-------------+-----------+---------+----------+
| table_name |      index_name       | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+-----------------------+------------+--------------+-------------+-----------+---------+----------+
| logoff     | primary               |   false    |            1 | user_id     | ASC       |  false  |  false   |
| logoff     | logoff_user_email_key |   false    |            1 | user_email  | ASC       |  false  |  false   |
| logoff     | logoff_user_email_key |   false    |            2 | user_id     | ASC       |  false  |   true   |
+------------+-----------------------+------------+--------------+-------------+-----------+---------+----------+
(3 rows)

Create a table with secondary and inverted 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.

Inverted indexes allow efficient access to the schemaless data in a JSONB column.

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(),
    misc                 JSONB,     
    CONSTRAINT price_check CHECK (list_price >= min_price),
    INDEX date_added_idx (date_added),
    INDEX supp_id_prod_status_idx (supplier_id, product_status),
    INVERTED INDEX details (misc)
);

> SHOW INDEX FROM product_information;

+---------------------+--------------------------------------+------------+--------------+----------------+-----------+---------+----------+
|     table_name      |              index_name              | non_unique | seq_in_index |  column_name   | direction | storing | implicit |
+---------------------+--------------------------------------+------------+--------------+----------------+-----------+---------+----------+
| product_information | primary                              |   false    |            1 | product_id     | ASC       |  false  |  false   |
| product_information | product_information_product_name_key |   false    |            1 | product_name   | ASC       |  false  |  false   |
| product_information | product_information_product_name_key |   false    |            2 | product_id     | ASC       |  false  |   true   |
| product_information | product_information_catalog_url_key  |   false    |            1 | catalog_url    | ASC       |  false  |  false   |
| product_information | product_information_catalog_url_key  |   false    |            2 | product_id     | ASC       |  false  |   true   |
| product_information | date_added_idx                       |    true    |            1 | date_added     | ASC       |  false  |  false   |
| product_information | date_added_idx                       |    true    |            2 | product_id     | ASC       |  false  |   true   |
| product_information | supp_id_prod_status_idx              |    true    |            1 | supplier_id    | ASC       |  false  |  false   |
| product_information | supp_id_prod_status_idx              |    true    |            2 | product_status | ASC       |  false  |  false   |
| product_information | supp_id_prod_status_idx              |    true    |            3 | product_id     | ASC       |  false  |   true   |
| product_information | details                              |    true    |            1 | misc           | ASC       |  false  |  false   |
| product_information | details                              |    true    |            2 | product_id     | ASC       |  false  |   true   |
+---------------------+--------------------------------------+------------+--------------+----------------+-----------+---------+----------+
(12 rows)

We also have other resources on indexes:

• Create indexes for existing tables using CREATE INDEX.
• Learn more about indexes.

Create a table with auto-generated unique row IDs

To auto-generate unique row IDs, use the UUID column with the gen_random_uuid() function as the default value:

> CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);

> INSERT INTO t1 (name) VALUES ('a'), ('b'), ('c');

> SELECT * FROM t1;

+--------------------------------------+------+
|                  id                  | name |
+--------------------------------------+------+
| 60853a85-681d-4620-9677-946bbfdc8fbc | c    |
| 77c9bc2e-76a5-4ebc-80c3-7ad3159466a1 | b    |
| bd3a56e1-c75e-476c-b221-0da9d74d66eb | a    |
+--------------------------------------+------+
(3 rows)

Alternatively, you can use the BYTES column with the uuid_v4() function as the default value instead:

> CREATE TABLE t2 (id BYTES PRIMARY KEY DEFAULT uuid_v4(), name STRING);

> INSERT INTO t2 (name) VALUES ('a'), ('b'), ('c');

> SELECT * FROM t2;

+---------------------------------------------------+------+
|                        id                         | name |
+---------------------------------------------------+------+
| "\x9b\x10\xdc\x11\x9a\x9cGB\xbd\x8d\t\x8c\xf6@vP" | a    |
| "\xd9s\xd7\x13\n_L*\xb0\x87c\xb6d\xe1\xd8@"       | c    |
| "\uac74\x1dd@B\x97\xac\x04N&\x9eBg\x86"           | b    |
+---------------------------------------------------+------+
(3 rows)

In either case, generated IDs will be 128-bit, large enough for there to be virtually no chance of generating non-unique values. Also, once the table grows beyond a single key-value range (more than 64MB by default), new IDs will be scattered across all of the table's ranges and, therefore, likely across different nodes. This means that multiple nodes will share in the load.

If it is important for generated IDs to be stored in the same key-value range, you can use an integer type with the unique_rowid() function as the default value, either explicitly or via the SERIAL pseudo-type:

> CREATE TABLE t3 (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);

> INSERT INTO t3 (name) VALUES ('a'), ('b'), ('c');

> SELECT * FROM t3;

+--------------------+------+
|         id         | name |
+--------------------+------+
| 293807573840855041 | a    |
| 293807573840887809 | b    |
| 293807573840920577 | c    |
+--------------------+------+
(3 rows)

Upon insert or upsert, the unique_rowid() function generates a default value from the timestamp and ID of the node executing the insert. Such time-ordered values are likely to be globally unique except in cases where a very large number of IDs (100,000+) are generated per node per second. Also, there can be gaps and the order is not completely guaranteed.

Create a table with a foreign key constraint

FOREIGN KEY constraints 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.

You can include a foreign key action to specify what happens when a column referenced by a foreign key constraint is updated or deleted. The default actions are ON UPDATE NO ACTION and ON DELETE NO ACTION.

In this example, we use ON DELETE CASCADE (i.e., when row referenced by a foreign key constraint is deleted, all dependent rows are also deleted).

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

> CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id) ON DELETE CASCADE
  );

> SHOW CREATE orders;

+------------+--------------------------------------------------------------------------+
| table_name |                             create_statement                             |
+------------+--------------------------------------------------------------------------+
| orders     | CREATE TABLE orders (                                                    |
|            |                                                                          |
|            |     id INT NOT NULL,                                                     |
|            |                                                                          |
|            |     customer_id INT NULL,                                                |
|            |                                                                          |
|            |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           |
|            |                                                                          |
|            |     CONSTRAINT fk_customer_id_ref_customers FOREIGN KEY (customer_id)    |
|            | REFERENCES customers (id) ON DELETE CASCADE,                             |
|            |                                                                          |
|            |     INDEX orders_auto_index_fk_customer_id_ref_customers (customer_id    |
|            | ASC),                                                                    |
|            |                                                                          |
|            |     FAMILY "primary" (id, customer_id)                                   |
|            |                                                                          |
|            | )                                                                        |
+------------+--------------------------------------------------------------------------+
(1 row)

> INSERT INTO customers VALUES (1, 'Lauren');

> INSERT INTO orders VALUES (1,1);

> DELETE FROM customers WHERE id = 1;

> SELECT * FROM orders;

+----+-------------+
| id | customer_id |
+----+-------------+
+----+-------------+

Create a table that mirrors key-value storage

CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. Although it is not possible to access the key-value store directly, you can mirror direct access using a "simple" table of two columns, with one set as the primary key:

> CREATE TABLE kv (k INT PRIMARY KEY, v BYTES);

When such a "simple" table has no indexes or foreign keys, INSERT/UPSERT/UPDATE/DELETE statements translate to key-value operations with minimal overhead (single digit percent slowdowns). For example, the following UPSERT to add or replace a row in the table would translate into a single key-value Put operation:

> UPSERT INTO kv VALUES (1, b'hello')

This SQL table approach also offers you a well-defined query language, a known transaction model, and the flexibility to add more columns to the table if the need arises.

Create a table from a SELECT statement

You can use the CREATE TABLE AS statement to create a new table from the results of a SELECT statement, for example:

> SELECT * FROM customers WHERE state = 'NY';

+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+

> CREATE TABLE customers_ny AS SELECT * FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;

+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+

Create a table with a computed column

In this example, let's create a simple table with a computed column:

> CREATE TABLE names (
    id INT PRIMARY KEY,
    first_name STRING,
    last_name STRING,
    full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
  );

Then, insert a few rows of data:

> INSERT INTO names (id, first_name, last_name) VALUES
    (1, 'Lola', 'McDog'),
    (2, 'Carl', 'Kimball'),
    (3, 'Ernie', 'Narayan');

> SELECT * FROM names;

+----+------------+-------------+----------------+
| id | first_name |  last_name  |   full_name    |
+----+------------+-------------+----------------+
|  1 | Lola       | McDog       | Lola McDog     |
|  2 | Carl       | Kimball     | Carl Kimball   |
|  3 | Ernie      | Narayan     | Ernie Narayan  |
+----+------------+-------------+----------------+

The full_name column is computed from the first_name and last_name columns without the need to define a view.

Create a table with partitions

Note:

The primary key required for partitioning is different from the conventional primary key. To define the primary key for partitioning, prefix the unique identifier(s) in the primary key with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions. See Partition using Primary Key for more details.

Create a table with partitions by list

In this example, we create a table and define partitions by list.

> CREATE TABLE students_by_list (
    id INT DEFAULT unique_rowid(),
    name STRING,
    email STRING,
    country STRING,
    expected_graduation_date DATE,
    PRIMARY KEY (country, id))
    PARTITION BY LIST (country)
      (PARTITION north_america VALUES IN ('CA','US'),
      PARTITION australia VALUES IN ('AU','NZ'),
      PARTITION DEFAULT VALUES IN (default));

Create a table with partitions by range

In this example, we create a table and define partitions by range.

> CREATE TABLE students_by_range (
   id INT DEFAULT unique_rowid(),
   name STRING,
   email STRING,                                                                                           
   country STRING,
   expected_graduation_date DATE,                                                                                      
   PRIMARY KEY (expected_graduation_date, id))
   PARTITION BY RANGE (expected_graduation_date)
      (PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
      PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE));

Show the definition of a table

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

> SHOW CREATE logoff;

+------------+----------------------------------------------------------+
| table_name |                     create_statement                     |
+------------+----------------------------------------------------------+
| logoff     | CREATE TABLE logoff (                                    |
|            |                                                          |
|            |     user_id INT NOT NULL,                                |
|            |                                                          |
|            |     user_email STRING NULL,                              |
|            |                                                          |
|            |     logoff_date DATE NULL,                               |
|            |                                                          |
|            |     CONSTRAINT "primary" PRIMARY KEY (user_id ASC),      |
|            |                                                          |
|            |     UNIQUE INDEX logoff_user_email_key (user_email ASC), |
|            |                                                          |
|            |     FAMILY "primary" (user_id, user_email, logoff_date)  |
|            |                                                          |
|            | )                                                        |
+------------+----------------------------------------------------------+
(1 row)

See also

• INSERT
• ALTER TABLE
• DELETE
• DROP TABLE
• RENAME TABLE
• SHOW TABLES
• SHOW COLUMNS
• Column Families
• Table-Level Replication Zones
• Define Table Partitions
• Online Schema Changes

Was this page helpful?
YesNo