Trabalhar com restrições de chave estrangeira no Aurora DSQL

Com as restrições de chave estrangeira no Aurora DSQL, é possível transferir a lógica de integridade referencial de uma aplicação para o banco de dados. O Aurora DSQL é compatível com as ações referenciais NO ACTION, RESTRICT, CASCADE, SET NULL e SET DEFAULT. Também é compatível com os tipos de correspondência MATCH FULL e MATCH SIMPLE e com restrições de chave estrangeira adiáveis. Para ver a análise completa da sintaxe, consulte Restrições de chave estrangeira.

Como o Aurora DSQL mantém a integridade referencial

O Aurora DSQL mantém a integridade referencial em duas etapas: verificação do snapshot à medida que a transação é executada e resolução de conflitos no momento da confirmação. Juntas, essas etapas garantem que uma transação confirmada nunca viole uma restrição de chave estrangeira.

Verificação do snapshot. Cada transação no Aurora DSQL é executada com base em um snapshot consistente do banco de dados obtido no momento de seu início. Quando você insere ou atualiza uma linha de referência, o Aurora DSQL lê a tabela referenciada no snapshot correspondente ao início da sua transação para confirmar que a chave referenciada existe. Quando você exclui ou atualiza uma chave referenciada, o Aurora DSQL lê a tabela de referência no snapshot da transação. Ele confirma que não existem linhas de referência (para RESTRICT) ou que a operação não deixa linhas órfãs (para NO ACTION). Como essa verificação lê o snapshot do início da transação, em vez de obter um bloqueio, outras transações podem continuar modificando as tabelas referenciada e de referência em paralelo.

Resolução no momento da confirmação. A verificação do snapshot garante que a restrição era válida no início da transação, mas não no período entre o início e a confirmação. Uma transação simultânea pode excluir a linha referenciada ou inserir uma linha de referência conflitante depois que sua transação tiver sido iniciada. Para resolver esses conflitos, o Aurora DSQL aplica implicitamente a cláusula KEY SHARE às linhas referenciadas para detectar se alguma alteração simultânea invalidou o snapshot. Se o Aurora DSQL detectar um conflito, ele fará a transação falhar com um erro de serialização. Consulte mais informações sobre como a cláusula KEY SHARE afeta transações simultâneas em Controle de simultaneidade no Aurora DSQL.

Cenários de exemplo

No cenário a seguir, a tabela orders tem uma restrição de chave estrangeira em sua coluna product_id, que faz referência à tabela products. Isso torna products a tabela referenciada e orders a tabela de referência.

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

Conflito: exclusão e inserção simultâneas

Nesse cenário, uma sessão exclui uma linha referenciada enquanto outra sessão insere uma linha de referência.

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

As duas sessões são executadas ao mesmo tempo. O Aurora DSQL resolve o conflito no momento da confirmação. Não é possível terminar com um pedido que aponte para um produto excluído.

Sem conflito: atualização de coluna que não é chave

Nesse cenário, uma sessão atualiza uma coluna que não é chave na linha referenciada enquanto outra sessão insere uma linha de referência.

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

A atualização de name (uma coluna que não é chave) não entra em conflito com a chave estrangeira em product_id. A linha de referência só precisa que as colunas de chave da linha referenciada permaneçam iguais.

Práticas recomendadas para chaves estrangeiras no Aurora DSQL