CREATE TABLE

CREATE TABLE defines a new table.

Supported syntax

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }

index_parameters in UNIQUE and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]

Identity columns

The GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) clause creates the column as an identity column. It will have an implicit sequence attached to it and in newly-inserted rows the column will automatically have values from the sequence assigned to it. Such a column is implicitly NOT NULL.

The clauses ALWAYS and BY DEFAULT determine how explicitly user-specified values are handled in INSERT and UPDATE commands.

In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence.

In an UPDATE command, if ALWAYS is selected, any update of the column to any value other than DEFAULT will be rejected. If BY DEFAULT is selected, the column can be updated normally. (There is no OVERRIDING clause for the UPDATE command.)

The sequence_options clause can be used to override the parameters of the sequence. The available options include those shown for CREATE SEQUENCE, plus SEQUENCE NAME name. Without SEQUENCE NAME, the system chooses an unused name for the sequence.

Storage mode

The optional STORAGE clause sets the storage mode for the column. Use these options to control the behavior of compression for variable-length data types such as JSON, JSONB, TEXT, VARCHAR, and BPCHAR.

Amazon Aurora DSQL compresses some data types when they exceed a certain size. To disable this behavior, use the PLAIN or EXTERNAL options.

Foreign key constraints

The REFERENCES and FOREIGN KEY clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If you omit the refcolumn list, Aurora DSQL uses the primary key of the reftable. Otherwise, the refcolumn list must refer to the columns of a non-deferrable unique or primary key constraint.

Aurora DSQL matches a value inserted into the referencing column(s) against the values of the referenced table and referenced columns using the given match type. There are two supported match types: MATCH FULL and MATCH SIMPLE (which is the default).

You can define a foreign key as either a column constraint or a table constraint:

Referential actions

When you change data in the referenced columns, Aurora DSQL performs actions on the data in the referencing table's columns. The ON DELETE clause specifies the action to perform when a transaction deletes a referenced row in the referenced table. Likewise, the ON UPDATE clause specifies the action to perform when a transaction updates a referenced column to a new value. If a transaction updates the row but doesn't change the referenced column, Aurora DSQL takes no action.

Aurora DSQL supports the following referential actions:

Match types

Aurora DSQL supports the following match types:

You can apply NOT NULL constraints to the referencing columns to prevent these cases from arising.

Deferrability

You can control when a foreign key constraint is checked by specifying its deferrability:

For more information about changing constraint check timing within a transaction, see SET CONSTRAINTS.

Foreign key constraint examples

Assume you have a table storing products:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Now you want a table storing orders of those products. You want to ensure that the orders table contains references to products that actually exist. Define a foreign key constraint in the orders table that references the products table:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Now you can't create orders with non-NULL product_no entries that don't appear in the products table.

In this situation, the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.

You can shorten the above command to:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

If you omit the column list, Aurora DSQL uses the primary key of the referenced table as the referenced column(s).

You can assign your own name for a foreign key constraint in the usual way:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer CONSTRAINT fk_product REFERENCES products,
    quantity integer
);

A foreign key can also constrain and reference a group of columns. It then needs to be written in table constraint form:

CREATE TABLE inventory (
    warehouse_id integer,
    product_no integer,
    quantity integer,
    PRIMARY KEY (warehouse_id, product_no)
);

CREATE TABLE shipments (
    shipment_id integer PRIMARY KEY,
    warehouse_id integer,
    product_no integer,
    FOREIGN KEY (warehouse_id, product_no) REFERENCES inventory (warehouse_id, product_no)
);

The number and types of the constrained columns need to be compatible with the number and types of the referenced columns.

A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables:

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

A foreign key constraint can reference the same table it belongs to. This is called a self-referential foreign key. For example, if you want rows of a table to represent nodes of a tree structure, you could write:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text
);

A top-level node would have NULL parent_id, while non-NULL parent_id entries are constrained to reference valid rows of the table.

You can specify referential actions to control what happens when a referenced row is deleted or updated. The following example uses ON DELETE RESTRICT to prevent deletion of a product that is still referenced by an order:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products ON DELETE RESTRICT,
    quantity integer
);

With RESTRICT, attempting to delete a product that has orders referencing it produces an error immediately. With the default NO ACTION, the check can be deferred to the end of the transaction if the constraint is declared DEFERRABLE.

To create a foreign key that can be deferred to the end of a transaction, use the DEFERRABLE option. This is useful when you need to insert rows in both tables within the same transaction regardless of order:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products DEFERRABLE INITIALLY DEFERRED,
    quantity integer
);

With DEFERRABLE INITIALLY DEFERRED, the constraint isn't checked until commit time. You can insert the order row before the product row exists, as long as the product row is present when the transaction commits.

To use MATCH FULL with a composite foreign key, which requires that all referencing columns are null together or all non-null together:

CREATE TABLE shipments (
    shipment_id integer PRIMARY KEY,
    warehouse_id integer,
    product_no integer,
    FOREIGN KEY (warehouse_id, product_no)
        REFERENCES inventory (warehouse_id, product_no) MATCH FULL
);