Trabajo con restricciones de clave externa en Aurora DSQL

Con las restricciones de clave externa de Aurora DSQL, puede ingresar la lógica de integridad referencial de una aplicación en la base de datos. Aurora DSQL admite las acciones referenciales NO ACTION, RESTRICT, CASCADE, SET NULL y SET DEFAULT. También admite los tipos de coincidencia MATCH FULL y MATCH SIMPLE, y las restricciones de clave externa aplazables. Para ver un desglose completo de la sintaxis, consulte Restricciones de clave externa.

Cómo Aurora DSQL mantiene la integridad referencial

Aurora DSQL mantiene la integridad referencial en dos pasos: la verificación de instantánea a medida que se ejecuta la transacción y la resolución de conflictos en el momento de la confirmación. En conjunto, estos pasos garantizan que una transacción confirmada nunca infrinja una restricción de clave externa.

Verificación de instantánea. Cada transacción en Aurora DSQL se ejecuta en una instantánea coherente de la base de datos tomada en el momento de su inicio. Al insertar o actualizar una fila de referencia, Aurora DSQL lee la tabla a la que se hace referencia en la instantánea de la hora de inicio de la transacción para confirmar que existe la clave a la que se hace referencia. Al eliminar o actualizar una clave a la que se hace referencia, Aurora DSQL lee la tabla de referencia en la instantánea de la transacción. Confirma que no existe ninguna fila de referencia (para RESTRICT) o que la operación no deja filas huérfanas (para NO ACTION). Como esta verificación lee desde la instantánea de la hora de inicio de la transacción en lugar de bloquearse, otras transacciones pueden seguir modificando las tablas referenciadas y de referencia en paralelo.

Resolución en el momento de la confirmación. La verificación de instantánea garantiza que la restricción se mantenga en el momento de inicio de la transacción, pero no entre el inicio y la confirmación. Una transacción simultánea puede eliminar la fila a la que se hace referencia o insertar una fila de referencia conflictiva una vez iniciada la transacción. Para resolver estos conflictos, Aurora DSQL aplica implícitamente la cláusula KEY SHARE a las filas referenciadas para detectar si algún cambio simultáneo invalidó la instantánea. Si Aurora DSQL detecta un conflicto, la transacción produce un error de serialización. Para obtener más información acerca del modo en que la cláusula KEY SHARE afecta a las transacciones simultáneas, consulte Control de simultaneidad en Aurora DSQL.

Ejemplos de escenarios

En el siguiente escenario, la tabla orders tiene una restricción de clave externa en su columna product_id que hace referencia a la tabla products. Esto convierte a products en la tabla referenciada y a orders en la tabla de referencia.

CREATE TABLE products (
    product_id integer PRIMARY KEY,
    name text,
    price numeric
);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_id integer REFERENCES products,
    quantity integer
);
INSERT INTO products VALUES (1, 'Widget', 9.99);
    

Conflicto: eliminar e insertar simultáneamente

En este escenario, una sesión elimina una fila referenciada mientras que otra sesión inserta una fila de referencia.

-- Session A
BEGIN;
DELETE FROM products WHERE product_id = 1;

-- Session B
BEGIN;
INSERT INTO orders VALUES (100, 1, 5);

-- Session A
COMMIT;  -- succeeds

-- Session B
COMMIT;  -- fails with serialization error
ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001)

Ambas sesiones se ejecutan de forma simultánea. Aurora DSQL resuelve el conflicto en el momento de la confirmación. No puede terminar con un pedido que apunte a un producto eliminado.

Sin conflictos: actualización de columnas sin clave

En este escenario, una sesión actualiza una columna sin clave en la fila referenciada mientras que otra sesión inserta una fila de referencia.

-- Session A
BEGIN;
UPDATE products SET name = 'Super Widget' WHERE product_id = 1;

-- Session B
BEGIN;
INSERT INTO orders VALUES (101, 1, 3);

-- Session A
COMMIT;  -- succeeds

-- Session B
COMMIT;  -- succeeds

La actualización de name (una columna sin clave) no entra en conflicto con la clave externa de product_id. A la fila de referencia solo le importa que las columnas de clave de la fila a la que se hace referencia permanezcan iguales.

Prácticas recomendadas con claves externas en Aurora DSQL