Define a new table.

​
Synopsis

CREATE TABLE [ SET { READY | NOT READY } ]

  table_name ([

    { column_name data_type [ column_constraint [ ... ] ] [ INDEX [ index_parameters] ]}

    [, ... ]

  ])

[ WITH ( storage_parameter [= value] [, ... ] ] )



where column_constraint is:

{ NULL |

NOT NULL |

DEFAULT default_expr |

UNIQUE |

PRIMARY KEY |

REFERENCES reftable [ (refcolumn) ] }

and index_parameters is:

WITH ( storage_parameter [= value] [, ... ] )

​
Description

The CREATE TABLE command is used to create a new empty table, with columns, indexes and constraints, in the current database.

Once created, unless set as NOT READY, the table’s definition is fixed and cannot be altered, additional indexes cannot be added or removed.

Index and constraint names are auto-generated by Regatta.

Note: If there is UNIQUE, PRIMARY KEY or REFERENCES constraints, then there must be an INDEX clause following it. Otherwise, there could be either zero or one INDEX clause. The INDEX clause must be after all the constraint clauses (if any).

​
Parameters

​
SET READY

Set the table as ready to accept Data Manipulation Language (DML) commands.

Once a table is set as ready, changes to its columns, constraints and indexes are not allowed. This is the default behavior.

​
SET NOT READY

Set the table as not ready to accept DML commands, and allow further changes to its definition such as creating new indexes and adding new columns.

Note: A table with zero columns can be created as long as it’s set to NOT READY. At least one column is required in order to set its state to READY.

​
table_name

The name of the table to be created.

The table name must not exist in the database, and conform with the Identifiers Limitations.

​
column_name

The name of the column to be created in the new table. Column names should be unique within the table and conform with the Identifiers Limitations.

​
data_type

The data type of the column. For more information on the supported data types, refer to Data Types.

​
NULL

The column is allowed to contain NULL values. This is the default.

​
NOT NULL

The column is not allowed to contain NULL values.

​
DEFAULT default_expr

An optional default value to set in the column level. If no default is specified then the default value is NULL. (Note that when the default value is NULL and NOT NULL constraint is specified, you cannot insert rows with the DEFAULT keyword.)

A possible default values are either a literal (e.g, DEFAULT 'my default' or DEFAULT 1) or a variable-free expression that returns a single value (suitable to store in a single table cell) that is being evaluated at the time the row is created (e.g., DEFAULT NOW() for current date/timestamp (depends on the column data type)). Note that expressions which include other columns in the current table are not allowed.

​
UNIQUE

The UNIQUE constraint specifies that the column can contain only unique values. The constraint therefore enforces that any two rows in the same table must differ in this column (note that Regatta is case-sensitive) (e.g., ‘Regatta’ <> ’REGATTA’ <> ‘regatta’).

Note: If the UNIQUE constraint is specified, the NOT NULL constraint is required.

Unique constraints implicitly create an index on that column, hence the index clause is required.

​
PRIMARY KEY

The PRIMARY KEY constraint specifies this column can contain only unique, non-NULL values. Only one primary key can be specified for a table. Since PRIMARY KEY implies UNIQUE and NOT NULL – if any of the latter two is specified for the same column, it is ignored (as it is already implied by the PRIMARY KEY constraint).

Note: PostgreSQL allows specifying the combination of PRIMARY KEY with NULL constraint, but it ignores the latter and requires only non-NULL values. In Regatta, specifying this contradictory combination of constraints results in an error.

A primary key constraint implicitly creates an index on the column, hence the index clause is required.

​
REFERENCES reftable [ refcolumn ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ]

This clause specifies a foreign key constraint and links data in that table column with another table primary key. The referenced column must be the PRIMARY KEY constraint in the referenced table, in the same database. With this constraint, a value inserted into the referencing column must match a row in the referenced table. If refcolumn is omitted, the primary key column of the reftable is used.

Reference constraints implicitly create an index on that column, hence the index clause is required.

A foreign key column is allowed to be NULL. If it is NULL, the row is not required to have a match in the referenced table.

Foreign key restrictions:

  1. The referencing column and the reference column must be of the same data type, and have the same type parameters (e.g. a VARCHAR(200) column cannot reference a VARCHAR(100) column.)
  2. A column cannot reference another column in the same table.
  3. A column can only reference a primary key column
  4. A column can reference at most one other column. In other words, a column cannot have more than one “foreign key” constraint.

ON DELETE this clause specifies the action to perform when a referenced row in the referenced table is being deleted.

ON UPDATE this clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value in the referenced column.

Referential_action

RESTRICT The DELETE/UPDATE fails if the check identifies that a foreign key constraint is about to be violated.

Updating a referenced column is not allowed, and the explicit RESTRICT option is supported for compatibility reasons (but that’s also the default behavior).

​
INDEX index_parameter

This clause specifies that an index shall be created for the pertinent column.

The index column size should not exceed the index maximum column size. See Database Limitations

This clause is required if REFERENCES, UNIQUE or PRIMARY KEY constraints are specified, and is optional otherwise.

​
Storage parameters

Storage parameters, specified after the keyword WITH, are optional for both tables and indexes. Possible storage parameters:

​
devices

A list of one or more device names, separated by commas, surrounded by parentheses. Device names shall be specified as they appear in the output of the SHOW DEVICES. This parameter is required for both tables and indexes.

​
range_separators

Specify key range separators for index segments.

If the number of devices (specified in the devices parameter) is 1, this parameter shall not be specified or shall be set to an empty list. If the number of devices is more than 1, this parameter is required and the number of values must be one less than the number of devices.

For example:

WITH (devices = (m1d2, m2d5, m3d3), range_separators = ('j', 'p'))

Notes:

  1. The range separators must be literal constants that match the column data type, and cannot be NULL.
  2. The list of values must be strictly monotonically increasing.
  3. The specified separator values are the first values in each range, starting with the second one. (In the example above, ‘j’ is the first value within the second range and ‘p’ is the first value in the third range).
  4. NULL values are always stored in the beginning of the first range.
  5. A device can appear multiple times in the device list, in such case multiple ranges will be stored on the same device.

If storage parameters are not specified for the table, the default is to create one segment of the table in each device in the system. The system will distribute the table rows across all the devices.

If storage parameters are not specified for an index, the default is to create a single segment for the index on a randomly chosen device.

​
Examples

Create a table with a primary key:

CREATE TABLE customers (

  c_custkey INT PRIMARY KEY INDEX WITH (devices = (m1d2)),

  c_name VARCHAR(40) NOT NULL

    INDEX WITH (devices = (m1d2, m2d5, m3d3), range_separators = ('j', 'p')),

  c_gender CHAR(1)

) WITH (devices = (m1d2, m2d5, m3d3));

Create the table customers and set it ready for DML. Set the column c_custkey as the primary key, defined with an index, and the c_name column with the NOT NULL constraint.

The primary key index is using the device (m1d2) - note that it has only 1 device therefore the RANGE_SEPARATORS clause is not needed.

The column c_name has an index on it with the following devices:

  • Device m1d2 stores values lower than or equal to ‘j’
  • Device m2d5 stores values greater than ‘j’ and lower than or equal to ‘p’,
  • Device m3d3 stores values greater than ‘p’.

The table data is stored on the devices (m1d2, m2d5, m3d3).

Create a table with a foreign key:

CREATE TABLE SET NOT READY orders (

  o_order_id CHAR(10) PRIMARY KEY INDEX WITH (devices = (m1d2)),

  o_name VARCHAR(120) NOT NULL,

  o_custkey INT NOT NULL

    REFERENCES customers INDEX WITH (devices = (m1d3)),

  o_totalprice DECIMAL (5,2),

  o_date date DEFAULT NOW() NOT NULL

) WITH (devices = (m1d4));

Create the table orders with a foreign key referencing the primary key of the customers table.

Set the table as NOT READY for DML, so it can still be modified with the ALTER TABLE command.

The table primary key (o_order_id) is using an index stored on device m1d2

The o_name and o_date columns have a constraint prohibiting NULL values. The o_date also has a default value of the current date (by using the function NOW()).

The o_custkey column is a foreign key to the primary key of table customers. It has the NOT NULL constraint, and it must contain values that exist in the primary key (c_custkey) in the table customers. This foreign key column has an index, stored on the device m1d3.

The table itself is stored on the device m1d4.

Create a table with a calculated column:

CREATE TABLE transactions (

  transactions_id INT PRIMARY KEY INDEX WITH (devices = (m1d2)),

  transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

  product_name VARCHAR(100) NOT NULL,

  purchse_price FLOAT NOT NULL,

  sale_price FLOAT,

  number_of_sold_items INT,

  profit FLOAT

) WITH (devices = (m1d2, m2d5, m3d3));

Create the table transactions. Set the transaction_date column with the default of setting the current time.

​
Compatibility

The CREATE TABLE command conforms to the SQL standard, with the following exceptions:

  1. SET READY | NOT READY is a Regatta extension.
  2. The WITH clause is a Regatta extension; storage parameters are not in the standard.