CREATE TABLE
Synopsis
Use the CREATE TABLE statement to create a new table in a database. It defines the table name, column names and types, primary key, and table properties.
Syntax
create_table ::= CREATE [ TEMPORARY | TEMP ] TABLE [ IF NOT EXISTS ]
table_name ( [ table_elem [ , ... ] ] )
[ WITH ( storage_parameters ) | WITHOUT OIDS ]
table_elem ::= column_name data_type [ column_constraint [ ... ] ]
| table_constraint
column_constraint ::= [ CONSTRAINT constraint_name ]
{ NOT NULL
| NULL
| CHECK ( expression )
| DEFAULT expression
| UNIQUE index_parameters
| PRIMARY KEY
| references_clause }
table_constraint ::= [ CONSTRAINT constraint_name ]
{ CHECK ( expression )
| UNIQUE ( column_names ) index_parameters
| PRIMARY KEY ( key_columns )
| FOREIGN KEY ( column_names )
references_clause }
key_columns ::= hash_columns [ , range_columns ] | range_columns
hash_columns ::= column_name [ HASH ] | ( column_name [ , ... ] ) HASH
range_columns ::= { column_name { ASC | DESC } } [ , ... ]
storage_parameters ::= storage_parameter [ , ... ]
storage_parameter ::= param_name [ = param_value ]
index_parameters ::= [ INCLUDE ( column_names ) ]
[ WITH ( storage_parameters ) ]
references_clause ::= REFERENCES table_name [ column_name [ , ... ] ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE key_action ]
[ ON UPDATE key_action ]
create_table
table_elem
column_constraint
table_constraint
key_columns
hash_columns
range_columns
storage_parameters
storage_parameter
index_parameters
references_clause
Semantics
Create a table with table_name. If qualified_name already exists in the specified database, an error will be raised unless the IF NOT EXISTS clause is used.
Primary key
Primary key can be defined in either column_constraint or table_constraint, but not in both.
There are two types of primary key columns:
Hash primary key columns: The primary key may have zero or more leading hash-partitioned columns. By default, only the first column is treated as the hash-partition column. But this behavior can be modified by explicit use of the HASH annotation.Range primary key columns: A table can have zero or more range primary key columns and it controls the top-level ordering of rows within a table (if there are no hash partition columns) or the ordering of rows among rows that share a common set of hash partitioned column values. By default, the range primary key columns are stored in ascending order. But this behavior can be controlled by explicit use ofASCorDESC.
For example, if the primary key specification is PRIMARY KEY ((a, b) HASH, c DESC) then columns a & b are used together to hash partition the table, and rows that share the same values for a and b are stored in descending order of their value for c.
If the primary key specification is PRIMARY KEY(a, b), then column a is used to hash partition
the table and rows that share the same value for a are stored in ascending order of their value
for b.
Foreign key
FOREIGN KEY and REFERENCES specifies that the set of columns can only contain values that are present in the referenced column(s) of the referenced table. It is used to enforce referential integrity of data.
Unique
This enforces that the set of columns specified in the UNIQUE constraint are unique in the table, that is, no two rows can have the same values for the set of columns specified in the UNIQUE constraint.
Check
This is used to enforce that data in the specified table meets the requirements specified in the CHECK clause.
Default
This clause is used to specify a default value for the column. If an INSERT statement does not specify a value for the column, then the default value is used. If no default is specified for a column, then the default is NULL.
Temporary or Temp
Using this qualifier will create a temporary table. Temporary tables are only visible in the current client session or transaction in which they are created and are automatically dropped at the end of the session or transaction. Any indexes created on temporary tables are temporary as well.
Storage parameters
The tablets property specifies the number of tablets to be used. This is useful for two data center (2DC) deployments. See example below: Create CDC table specifying number of tablets. Use the AND operator to use multiple table properties.
Other storage parameters as defined by PostgreSQL are ignored and only present for compatibility with PostgreSQL.
Examples
Table with primary key
yugabyte=# CREATE TABLE sample(k1 int,
k2 int,
v1 int,
v2 text,
PRIMARY KEY (k1, k2));In this example, the first column k1 will be HASH, while second column k2 will be ASC.
yugabyte=# \d sample
Table "public.sample"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
k1 | integer | | not null |
k2 | integer | | not null |
v1 | integer | | |
v2 | text | | |
Indexes:
"sample_pkey" PRIMARY KEY, lsm (k1 HASH, k2)
Table with range primary key
yugabyte=# CREATE TABLE range(k1 int,
k2 int,
v1 int,
v2 text,
PRIMARY KEY (k1 ASC, k2 DESC));Table with check constraint
yugabyte=# CREATE TABLE student_grade(student_id int,
class_id int,
term_id int,
grade int CHECK (grade >= 0 AND grade <= 10),
PRIMARY KEY (student_id, class_id, term_id));Table with default value
yugabyte=# CREATE TABLE cars(id int PRIMARY KEY,
brand text CHECK (brand in ('X', 'Y', 'Z')),
model text NOT NULL,
color text NOT NULL DEFAULT 'WHITE' CHECK (color in ('RED', 'WHITE', 'BLUE')));Table with foreign key constraint
Define two tables with a foreign keys constraint.
yugabyte=# CREATE TABLE products(id int PRIMARY KEY,
descr text);
yugabyte=# CREATE TABLE orders(id int PRIMARY KEY,
pid int REFERENCES products(id) ON DELETE CASCADE,
amount int);Insert some rows.
yugabyte=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
yugabyte=# INSERT INTO products VALUES (1, 'Phone X'), (2, 'Tablet Z');
yugabyte=# INSERT INTO orders VALUES (1, 1, 3), (2, 1, 3), (3, 2, 2);
yugabyte=# SELECT o.id AS order_id, p.id as product_id, p.descr, o.amount FROM products p, orders o WHERE o.pid = p.id;order_id | product_id | descr | amount
----------+------------+----------+--------
1 | 1 | Phone X | 3
2 | 1 | Phone X | 3
3 | 2 | Tablet Z | 2
(3 rows)
Inserting a row referencing a non-existent product is not allowed.
yugabyte=# INSERT INTO orders VALUES (1, 3, 3);ERROR: insert or update on table "orders" violates foreign key constraint "orders_pid_fkey"
DETAIL: Key (pid)=(3) is not present in table "products".
Deleting a product will cascade to all orders (as defined in the CREATE TABLE statement above).
yugabyte=# DELETE from products where id = 1;
yugabyte=# SELECT o.id AS order_id, p.id as product_id, p.descr, o.amount FROM products p, orders o WHERE o.pid = p.id; order_id | product_id | descr | amount
----------+------------+----------+--------
3 | 2 | Tablet Z | 2
(1 row)
Table with unique constraint
yugabyte=# CREATE TABLE translations(message_id int UNIQUE,
message_txt text);Create CDC table specifying number of tablets
For two data center (2DC) deployments that require the identical number of tablets on both clusters, you can use the CREATE TABLE statement with the WITH clause to specify the number of tablets.
yugabyte=# CREATE TABLE tracking (id int PRIMARY KEY) WITH tablets = 10;If you create an index for these tables, you can also specify the number of tablets for the index.
You can also use AND to add other table properties, like in this example.
yugabyte=# CREATE TABLE tracking (id int PRIMARY KEY) WITH tablets = 10 AND transactions = { 'enabled' : true };