CREATE TABLE

CREATE TABLE は、新しいテーブルを定義します。

サポートされている構文

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 [, ... ] ) ]

アイデンティティ列

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) 句は、列を ID 列として作成します。暗黙的なシーケンスがアタッチされ、新しく挿入された行の列には、割り当てられたシーケンスの値が自動的に設定されます。このような列は暗黙的に NOT NULL です。

ALWAYS 句と BY DEFAULT 句は、INSERT コマンドと UPDATE コマンドでユーザーが明示的に指定した値がどのように処理されるかを決定します。

INSERT コマンドで ALWAYS を選択した場合、ユーザー指定の値は、INSERT ステートメントで OVERRIDING SYSTEM VALUE が指定されている場合にのみ受け入れられます。BY DEFAULT が選択されている場合、ユーザー指定の値が優先されます。

UPDATE コマンドで ALWAYS を選択した場合、列を DEFAULT 以外の値に更新することは拒否されます。BY DEFAULT を選択した場合、列は通常どおり更新できます。(UPDATE コマンドには OVERRIDING 句はありません)。

sequence_options 句を使用して、シーケンスのパラメータを上書きできます。使用可能なオプションには、CREATE SEQUENCE に表示されているオプションに加えて、SEQUENCE NAME name が含まれます。SEQUENCE NAME がない場合、システムはシーケンスに未使用の名前を選択します。

ストレージモード

オプションの STORAGE 句は、列のストレージモードを設定します。これらのオプションを使用して、JSON、JSONB、TEXT、VARCHAR、BPCHAR などの可変長データ型の圧縮動作を制御します。

Amazon Aurora DSQL は、特定のサイズを超えると、一部のデータ型を圧縮します。この動作を無効にするには、PLAIN または EXTERNAL オプションを使用します。

外部キー制約

REFERENCES 句と FOREIGN KEY 句は外部キー制約を指定します。この制約の場合、新しいテーブルの 1 つ以上の列のグループには、参照されるテーブルの行の参照される列の値と一致する値のみを含める必要があります。refcolumn リストを省略すると、Aurora DSQL は reftable のプライマリキーを使用します。それ以外の場合、refcolumn リストは、非遅延の一意制約またはプライマリキー制約の列を参照する必要があります。

Aurora DSQL は、指定された一致タイプを使用して、参照する列に挿入された値を、参照されるテーブルおよび参照される列の値と照合します。サポートされている一致タイプは MATCH FULL と MATCH SIMPLE (デフォルト) の 2 つです。

外部キーは、列制約またはテーブル制約として定義できます。

参照アクション

参照される列のデータを変更すると、Aurora DSQL は参照するテーブルの列のデータに対してアクションを実行します。ON DELETE 句は、トランザクションが参照されるテーブル内の参照される行を削除するときに実行するアクションを指定します。同様に、ON UPDATE 句は、トランザクションが参照される列を新しい値に更新するときに実行するアクションを指定します。トランザクションが行を更新しても、参照される列が変更されない場合、Aurora DSQL は何もアクションを実行しません。

Aurora DSQL は、以下の参照アクションをサポートしています。

一致タイプ

Aurora DSQL は、以下の一致タイプをサポートしています。

参照する列に NOT NULL 制約を適用して、このようなケースの発生を防ぐことができます。

遅延可能性

外部キー制約をチェックするタイミングを制御するには、遅延可能性を指定します。

トランザクション内における制約チェックのタイミング変更の詳細については、「SET CONSTRAINTS」を参照してください。

外部キー制約の例

製品を格納するテーブルがあるとします。

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

次に、これらの製品の注文を格納するテーブルを作成します。注文テーブルに、実際に存在する製品への参照が含まれていることを保証する必要があります。注文テーブルに、製品テーブルを参照する外部キー制約を定義します。

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

これで、製品テーブルに存在しない NULL 以外の product_no エントリを含む注文を作成することはできなくなりました。

この場合、注文テーブルは参照するテーブルであり、製品テーブルは参照されるテーブルです。同様に、参照する列と参照される列があります。

上記のコマンドは次のように短縮できます。

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

列リストを省略すると、Aurora DSQL は参照されるテーブルのプライマリキーを、参照される列として使用します。

外部キー制約には、通常の方法で独自の名前を割り当てることができます。

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

外部キーは列のグループを制約および参照することもできます。その場合は、テーブル制約形式で記述する必要があります。

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)
);

制約対象となる列の数および型は、参照される列の数および型と互換性がある必要があります。

テーブルには、複数の外部キー制約を含めることができます。これは、テーブル間の多対多リレーションシップを実装するために使用します。

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

外部キー制約は、それが属するテーブル自体を参照できます。これは、自己参照外部キーと呼ばれます。例えば、テーブルの行をツリー構造のノードとして表す場合は、次のように記述できます。

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

最上位ノードは NULL の parent_id になりますが、NULL 以外の parent_id エントリはテーブルの有効な行を参照するように制約されます。

参照アクションを指定して、参照される行が削除または更新されたときに何が起こるかを制御できます。次の例では、ON DELETE RESTRICT を使用して、注文によってまだ参照されている製品の削除を防止しています。

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

RESTRICT では、注文で参照している製品を削除しようとすると、すぐにエラーが発生します。デフォルトの NO ACTION では、制約が DEFERRABLE と宣言されている場合、チェックをトランザクションの最後まで遅延できます。

トランザクションの最後まで遅延できる外部キーを作成するには、DEFERRABLE オプションを使用します。これは、順序に関係なく、同じトランザクション内で両方のテーブルに行を挿入する必要がある場合に便利です。

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

DEFERRABLE INITIALLY DEFERRED では、制約はコミット時までチェックされません。トランザクションのコミット時に製品行が存在していれば、製品行が存在する前に注文行を挿入できます。

複合外部キーで MATCH FULL を使用するには、すべての参照する列が同時に null であるか、すべてが同時に null でない必要があります。

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
);