CREATE TABLE

CREATE TABLE define uma nova tabela.

Sintaxe compatível

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

Colunas de identidade

A cláusula GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) cria a coluna como uma coluna de identidade. Ela terá uma sequência implícita anexada a ela e, nas linhas recém-inseridas, a coluna terá automaticamente valores da sequência atribuída a ela. Essa coluna é implicitamente NOT NULL.

As cláusulas ALWAYS e BY DEFAULT determinam como os valores especificados pelo usuário são tratados explicitamente nos comandos INSERT e UPDATE.

Em um comando INSERT, se ALWAYS estiver selecionado, um valor especificado pelo usuário só será aceito se a instrução INSERT especificarOVERRIDING SYSTEM VALUE. Se BY DEFAULT estiver selecionado, o valor especificado pelo usuário terá precedência.

Em um comando UPDATE, se ALWAYS estiver selecionado, qualquer atualização da coluna para qualquer valor diferente de DEFAULT será rejeitada. Se BY DEFAULT estiver selecionado, a coluna poderá ser atualizada normalmente. (Não há nenhuma cláusula OVERRIDING para o comando UPDATE.)

A cláusula sequence_options pode ser usada para substituir os parâmetros da sequência. As opções disponíveis incluem aquelas mostradas para CREATE SEQUENCE, mais SEQUENCE NAME name. Sem SEQUENCE NAME, o sistema escolherá um nome não utilizado para a sequência.

Modo de armazenamento

A cláusula opcional STORAGE define o modo de armazenamento da coluna. Use estas opções para controlar o comportamento da compactação de tipos de dados de comprimento variável, como JSON, JSONB, TEXT, VARCHAR e BPCHAR.

O Amazon Aurora DSQL compacta alguns tipos de dados quando eles excedem um determinado tamanho. Para desativar esse comportamento, use as opções PLAIN ou EXTERNAL.

Restrições de chave estrangeira

As cláusulas REFERENCES e FOREIGN KEY especificam uma restrição de chave estrangeira, que exige que um grupo de uma ou mais colunas da nova tabela contenha somente valores que correspondam a valores nas colunas referenciadas de alguma linha da tabela referenciada. Se você omitir a lista refcolumn, o Aurora DSQL usará a chave primária da reftable. Caso contrário, a lista refcolumn deverá fazer referência às colunas de uma restrição exclusiva ou de chave primária que não seja adiável.

O Aurora DSQL compara um valor inserido nas colunas de referência com os valores da tabela referenciada e das colunas referenciadas usando o tipo de correspondência especificado. Há suporte para dois tipos de correspondência: MATCH FULL e MATCH SIMPLE (que é o padrão).

Você pode definir uma chave estrangeira como uma restrição de coluna ou uma restrição de tabela:

Ações referenciais

Quando você altera dados nas colunas referenciadas, o Aurora DSQL executa ações nos dados das colunas da tabela de referência. A cláusula ON DELETE especifica a ação a ser executada quando uma transação exclui uma linha referenciada na tabela referenciada. Da mesma forma, a cláusula ON UPDATE especifica a ação a ser executada quando uma transação atualiza uma coluna referenciada para um novo valor. Se uma transação atualizar a linha, mas não alterar a coluna referenciada, o Aurora DSQL não fará nenhuma ação.

O Aurora DSQL é compatível com as seguintes ações referenciais:

Tipos de correspondência

O Aurora DSQL é compatível com os seguintes tipos de correspondência:

Você pode aplicar restrições NOT NULL às colunas de referência para impedir que esses casos ocorram.

Possibilidade de adiamento

É possível controlar quando uma restrição de chave estrangeira é verificada especificando sua possibilidade de adiamento:

Consulte mais informações sobre como alterar o momento da verificação de restrições dentro de uma transação em SET CONSTRAINTS.

Exemplos de restrições de chave estrangeira

Suponha que você tenha uma tabela que armazene produtos:

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

Agora você deseja criar uma tabela que armazene pedidos desses produtos. Você deseja garantir que a tabela de pedidos contenha referências a produtos que realmente existam. Defina uma restrição de chave estrangeira na tabela de pedidos que faça referência à tabela de produtos:

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

Agora você não pode criar pedidos com entradas product_no não nulas que não apareçam na tabela de produtos.

Nessa situação, a tabela de pedidos é a tabela de referência e a tabela de produtos é a tabela referenciada. Da mesma forma, existem colunas de referência e colunas referenciadas.

Você pode abreviar o comando acima para:

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

Se você omitir a lista de colunas, o Aurora DSQL usará a chave primária da tabela referenciada como as colunas referenciadas.

Você pode atribuir seu próprio nome a uma restrição de chave estrangeira da maneira usual:

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

Uma chave estrangeira também pode restringir e fazer referência a um grupo de colunas. Nesse caso, ela precisa ser escrita na forma de uma restrição de tabela:

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

O número e os tipos das colunas restringidas precisam ser compatíveis com o número e os tipos das colunas referenciadas.

Uma tabela pode ter mais de uma restrição de chave estrangeira. Isso é usado para implementar relações de muitos-para-muitos entre tabelas:

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

Uma restrição de chave estrangeira também pode fazer referência à mesma tabela à qual pertence. Isso é chamado de chave estrangeira autorreferente. Por exemplo, se você quiser que as linhas de uma tabela representem nós de uma estrutura de árvore, poderá escrever:

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

Um nó de nível superior teria NULL parent_id, enquanto as entradas parent_id não nulas seriam restritas a fazer referência a linhas válidas da tabela.

Você pode especificar ações referenciais para controlar o que acontece quando uma linha referenciada é excluída ou atualizada. O exemplo a seguir usa ON DELETE RESTRICT para impedir a exclusão de um produto que ainda seja referenciado por um pedido:

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

Com RESTRICT, tentar excluir um produto que tenha pedidos que façam referência a ele gera um erro imediatamente. Com o padrão NO ACTION, a verificação pode ser adiada até o final da transação se a restrição for declarada como DEFERRABLE.

Para criar uma chave estrangeira que possa ser adiada até o final de uma transação, use a opção DEFERRABLE. Isso é útil quando você precisa inserir linhas em ambas as tabelas dentro da mesma transação, independentemente da ordem:

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

Com DEFERRABLE INITIALLY DEFERRED, a restrição não é verificada até o momento da confirmação. Você pode inserir a linha do pedido antes que a linha do produto exista, desde que a linha do produto esteja presente quando a transação for confirmada.

Para usar MATCH FULL com uma chave estrangeira composta, o que exige que todas as colunas de referência sejam nulas juntas ou todas não sejam nulas juntas:

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