CREATE TABLE

CREATE TABLE define una nueva tabla.

Sintaxis admitida

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

Columnas de identidad

La cláusula GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY ( sequence_options ) crea la columna como una columna de identidad. Tendrá una secuencia implícita asociada y, en las filas recién insertadas, la columna tendrá automáticamente los valores de la secuencia que se le haya asignado. Dicha columna es implícitamente NOT NULL.

Las cláusulas ALWAYS y BY DEFAULT determinan cómo se gestionan explícitamente los valores especificados por el usuario en los comandos INSERT y UPDATE.

En un comando INSERT, si se selecciona ALWAYS, solo se acepta un valor especificado por el usuario si la instrucción INSERT especifica OVERRIDING SYSTEM VALUE. Si se selecciona BY DEFAULT, prevalece el valor especificado por el usuario.

En un comando UPDATE, si se selecciona ALWAYS, cualquier actualización de la columna a un valor distinto de DEFAULT se rechazará. Si se selecciona BY DEFAULT, la columna se puede actualizar de forma normal. (No hay una cláusula OVERRIDING para el comando UPDATE).

La cláusula sequence_options se puede utilizar para anular los parámetros de la secuencia. Las opciones disponibles incluyen las que se muestran para CREATE SEQUENCE más SEQUENCE NAME name. Sin SEQUENCE NAME, el sistema elige un nombre no utilizado para la secuencia.

Modo de almacenamiento

La cláusula STORAGE opcional establece el modo de almacenamiento de la columna. Utilice estas opciones para controlar el comportamiento de la compresión para tipos de datos de longitud variable, como JSON, JSONB, TEXT, VARCHAR y BPCHAR.

Amazon Aurora DSQL comprime algunos tipos de datos cuando superan un tamaño determinado. Para deshabilitar este comportamiento, utilice las opciones PLAIN o EXTERNAL.

Restricciones de clave externa

Las cláusulas REFERENCES y FOREIGN KEY especifican una restricción de clave externa, que indica que un grupo de una o más columnas de la nueva tabla debe contener solo valores que coincidan con los valores en la columna o columnas referenciadas de alguna fila de la tabla referenciada. Si omite la lista de refcolumn, Aurora DSQL utiliza la clave principal de reftable. De lo contrario, la lista de refcolumn debe hacer referencia a las columnas de una restricción de clave principal o única no aplazable.

Aurora DSQL compara un valor insertado en las columnas de referencia con los valores de la tabla a la que se hace referencia y las columnas a las que se hace referencia mediante el tipo de coincidencia indicado. Se admiten dos tipos de coincidencia: MATCH FULL y MATCH SIMPLE (que es el predeterminado).

Puede definir una clave externa como una restricción de columna o una restricción de tabla:

Acciones de referencia

Al cambiar los datos de las columnas a las que se hace referencia, Aurora DSQL realiza acciones en los datos de las columnas de la tabla de referencia. La cláusula ON DELETE especifica la acción que se debe realizar cuando una transacción elimina una fila a la que se hace referencia en la tabla a la que se hace referencia. Del mismo modo, la cláusula ON UPDATE especifica la acción que se debe realizar cuando una transacción actualiza una columna a la que se hace referencia a un nuevo valor. Si una transacción actualiza la fila pero no cambia la columna a la que se hace referencia, Aurora DSQL no realiza ninguna acción.

Aurora DSQL admite las siguientes acciones de referencia:

Tipos de coincidencia

Aurora DSQL admite los siguientes tipos de coincidencia:

Puede aplicar restricciones NOT NULL a las columnas de referencia para evitar que se produzcan estos casos.

Aplazabilidad

Puede controlar cuándo se comprueba una restricción de clave externa especificando su aplazabilidad:

Para obtener más información sobre cómo cambiar el tiempo de verificación de restricciones dentro de una transacción, consulte SET CONSTRAINTS.

Ejemplos de restricciones de clave externa

Suponga que tiene una tabla que almacena productos:

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

Ahora quiere una tabla que almacene los pedidos de esos productos. Desea asegurarse de que la tabla de pedidos contenga referencias a productos que realmente existen. Defina una restricción de clave externa en la tabla de pedidos que haga referencia a la tabla de productos:

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

Ahora no puede crear pedidos con entradas product_no no NULL que no aparezcan en la tabla de productos.

En esta situación, la tabla de pedidos es la tabla de referencia y la tabla de productos es la tabla referenciada. Del mismo modo, hay columnas de referencia y columnas referenciadas.

Puede acortar el comando anterior a:

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

Si omite la lista de columnas, Aurora DSQL utiliza la clave principal de tabla a la que se hace referencia como las columnas referenciadas.

Puede asignar su propio nombre a una restricción de clave externa de la forma habitual:

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

Una clave externa también puede restringir y hacer referencia a un grupo de columnas. Luego, debe escribirse en un formulario de restricción de tabla:

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

El número y los tipos de las columnas restringidas deben ser compatibles con el número y los tipos de las columnas a las que se hace referencia.

Una tabla puede tener más de una restricción de clave externa. Esto se usa para implementar las relaciones de varios a varios entre las tablas:

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

Una restricción de clave externa puede hacer referencia a la misma tabla a la que pertenece. Esto se llama clave externa autorreferencial. Por ejemplo, si desea que las filas de una tabla representen los nodos de una estructura de árbol, puede escribir:

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

Un nodo de nivel superior tendría un valor NULL parent_id, mientras que las entradas parent_id que no sean NULL están restringidas a hacer referencia a filas válidas de la tabla.

Puede especificar acciones referenciales para controlar lo que ocurre cuando se elimina o actualiza una fila referenciada. En el ejemplo siguiente, se usa ON DELETE RESTRICT para evitar que se elimine un producto al que todavía hace referencia un pedido:

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

Con RESTRICT, si se intenta eliminar un producto cuyos pedidos le hacen referencia, se produce un error inmediato. Con el valor predeterminado NO ACTION, la comprobación se puede aplazar hasta el final de la transacción si la restricción se declara DEFERRABLE.

Para crear una clave externa que pueda aplazarse hasta el final de una transacción, utilice la opción DEFERRABLE. Esto resulta útil cuando necesita insertar filas en ambas tablas dentro de la misma transacción, independientemente del orden:

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

Con DEFERRABLE INITIALLY DEFERRED, la restricción no se comprueba hasta el momento de la confirmación. Puede insertar la fila de pedidos antes de que exista la fila de productos, siempre que la fila de productos esté presente cuando se confirme la transacción.

Para usar MATCH FULL con una clave externa compuesta, que requiere que todas las columnas de referencia sean nulas juntas o todas no 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
);