Integridade Referencial e suas Restrições
Este artigo tem como objetivo esclarecer os conceitos e citar exemplos práticos dos tipos de FOREIGN KEY para garantir uma integridade referencial de acordo com a sua regra de negócio. As formas abaixo são úteis no dia a dia de cada desenvolvedor/DBA.
O Oracle permite 3 tipos diferentes de integridades referenciais, sendo elas:
- ON DELETE CASCADE, ON DELETE SET NULL E ON DELETE NO ACTION (PADRÃO).
Para exemplificar nosso caso iremos criar 4 tabelas (CLIENTE, PRODUTO, PEDIDO e ITEM_PEDIDO).
PARA NOSSO TESTE CRIAMOS UM SCHEMA/USUARIO CHAMADO GPO COM A SENHA gpo12345
Para reproduzir nosso script você deve criá-lo e abrir uma conexão através dele, ou então alterar para outro na criação das tabelas e inserts.
-- CRIAR USUÁRIO CREATE USER GPO IDENTIFIED BY gpo12345; -- DAR PERMISSÃO PARA O USUÁRIO UTILIZAR A TABLESPACE SYSTEM ALTER USER GPO QUOTA UNLIMITED ON SYSTEM; -- DDL for Table CLIENTE CREATE TABLE GPO.CLIENTE ( ID_CLIENTE NUMBER NOT NULL , NO_CLIENTE VARCHAR(80) NOT NULL , NR_CPF VARCHAR(11) NOT NULL , DT_NASCIMENTO DATE , CONSTRAINT PK_CLIENTE PRIMARY KEY (ID_CLIENTE) ); INSERT INTO GPO.CLIENTE (ID_CLIENTE,NO_CLIENTE, NR_CPF, DT_NASCIMENTO) VALUES (1, 'JOÃO DA SILVA', '12345678910',TO_DATE('01/01/1990','dd/mm/yyyy')); INSERT INTO GPO.CLIENTE (ID_CLIENTE,NO_CLIENTE, NR_CPF, DT_NASCIMENTO) VALUES (2, 'MARIA PEREIRA', '10987654321',TO_DATE('01/01/1970','dd/mm/yyyy')); INSERT INTO GPO.CLIENTE (ID_CLIENTE,NO_CLIENTE, NR_CPF, DT_NASCIMENTO) VALUES (3, 'JOSÉ FEREIRA', '15987423687',TO_DATE('01/01/1980','dd/mm/yyyy')); -- DDL for Table PRODUTOS CREATE TABLE GPO.PRODUTO ( ID_PRODUTO NUMBER NOT NULL , NO_PRODUTO VARCHAR(50) NOT NULL , DE_PRODUTO VARCHAR(500) NOT NULL , VL_UNITARIO NUMBER(9,2) NOT NULL , CONSTRAINT PK_PRODUTO PRIMARY KEY (ID_PRODUTO) ); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (1,'CAIXA DE SOM','Caixa de som 5.1',200); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (2,'MONITOR','Monitor TV 24"',350); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (3,'SMARTPHONE IPHONE 11','Smartphone Iphone 11 vermelho….',4900); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (4,'IMPRESSORA EPSON','Impressora EPSON tanque de tinta',1100); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (5,'VIDEO GAME X-BOX','Video Game X-box com 2 controles',1200); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (6,'MOUSE SEM FIO','Mouse sem fio com botões de atalho',500); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (7,'TECLADO SEM FIO','Teclado sem fio ergonomico',200); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (8,'HEADSET COM FIO','Headset com fio e microfone',120); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (9,'HEADSET BLUETOOH','Headset Bluetooh audio 5.1',150); INSERT INTO GPO.PRODUTO (ID_PRODUTO,NO_PRODUTO, DE_PRODUTO, VL_UNITARIO) VALUES (10,'MODEM ROTEADOR','Modem roteador 300Mbps',200); -- DDL for Table PEDIDO CREATE TABLE GPO.PEDIDO ( ID_PEDIDO NUMBER NOT NULL , ID_CLIENTE NUMBER , DT_PEDIDO DATE NOT NULL , VL_TOTAL_PEDIDO NUMBER(9,2) , CONSTRAINT PK_PEDIDO PRIMARY KEY (ID_PEDIDO) , CONSTRAINT FK_PEDIDO_CLIENTE FOREIGN KEY (ID_CLIENTE) REFERENCES GPO.CLIENTE (ID_CLIENTE) ON DELETE SET NULL ); INSERT INTO GPO.PEDIDO (ID_PEDIDO,ID_CLIENTE, DT_PEDIDO, VL_TOTAL_PEDIDO) VALUES (1,1,sysdate-10,650); INSERT INTO GPO.PEDIDO (ID_PEDIDO,ID_CLIENTE, DT_PEDIDO, VL_TOTAL_PEDIDO) VALUES (2,2,sysdate-20,5100); INSERT INTO GPO.PEDIDO (ID_PEDIDO,ID_CLIENTE, DT_PEDIDO, VL_TOTAL_PEDIDO) VALUES (3,1,sysdate-30,970); -- DDL for Table ITEM_PEDIDO CREATE TABLE GPO.ITEM_PEDIDO ( ID_ITEM_PEDIDO NUMBER NOT NULL , ID_PEDIDO NUMBER NOT NULL , ID_PRODUTO NUMBER NOT NULL , QT_ITEM NUMBER NOT NULL , VL_TOTAL NUMBER(9,2) , CONSTRAINT PK_ITEM_PEDIDO PRIMARY KEY (ID_ITEM_PEDIDO) , CONSTRAINT FK_ITEM_PEDIDO_PEDIDO FOREIGN KEY (ID_PEDIDO) REFERENCES GPO.PEDIDO (ID_PEDIDO) ON DELETE CASCADE , CONSTRAINT FK_ITEM_PEDIDO_PRODUTO FOREIGN KEY (ID_PRODUTO) REFERENCES GPO.PRODUTO (ID_PRODUTO) ON DELETE NO ACTION ); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (1,1,1,1,200); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (2,1,2,1,350); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (3,1,3,1,4900); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (4,1,4,1,1100); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (5,2,5,1,1200); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (6,2,1,1,200); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (7,2,3,1,4900); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (8,3,6,1,500); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (9,3,7,1,200); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (10,3,8,1,120); INSERT INTO GPO.ITEM_PEDIDO (ID_ITEM_PEDIDO, ID_PEDIDO,ID_PRODUTO, QT_ITEM, VL_TOTAL) VALUES (11,3,9,1,150);
ON DELETE CASCADE é utilizada quando queremos remover o registro pai e seus filhos também, ou seja, os registros a qual ele é referenciado. Essa não é uma prática comum, mas pode ser utilizada em alguns casos.
Esse caso seria útil caso quiséssemos excluir um pedido e seus itens vinculados de forma automática.
Nessa imagem mostramos todos os pedidos.
SELECT * FROM GPO.PEDIDO;

Nessa imagem mostramos os itens do pedido número 3.
SELECT * FROM GPO.ITEM_PEDIDO WHERE ID_PEDIDO = 3;

Agora vamos deletar o Pedido, que com sua constraint definida como ON DELETE CASCADE automaticamente irá excluir os registros vinculados na ITEM_PEDIDO.
SELECT * FROM GPO.ITEM_PEDIDO WHERE ID_PEDIDO = 3;

Atenção, constraints do tipo ON DELETE CASCADE devem ser utilizadas somente em casos específicos onde a regra de negócio da aplicação exija esse comportamento, pois a mesma aumenta o risco de perdas de dados.
ON {DELETE|UPDATE} SET NULL é suportado tanto para comandos DELETE quanto UPDATE, nesse caso quando é feito o delete ou update do registro pai, o registro filho será atualizado para NULL.
É importante lembrar que quando especificamos SET NULL não podemos declarar a coluna na tabela filha como NOT NULL.
Mas em qual caso isso seria útil?
Com a LGPD (Lei Geral de Proteção de Dados) em vigor qualquer pessoa tem direito de solicitar para qualquer empresa que apague seus dados pessoais do sistema.
Em nosso caso o cliente de código 2-Maria Pereira solicitou para que seus dados sejam apagados, mas nós não queremos perder o histórico de pedidos da Maria para que isso não afete os relatórios de vendas. Então nesse caso o ON DELETE SET NULL seria ótimo para nós. Veja as imagens.
Nessa imagem mostramos a tabela de cliente.

Nessa imagem mostramos todos os Pedidos, entre eles o pedido do cliente de código 2-Maria Pereira.
SELECT * FROM GPO.PEDIDO;

Agora deletamos o cliente.
DELETE FROM GPO.CLIENTE WHERE ID_CLIENTE = 2;

Verificando como ficou o pedido.
SELECT * FROM GPO.PEDIDO;

Atenção, constraints do tipo ON {DELETE|UPDATE} SET NULL devem ser utilizadas somente em casos específicos onde a regra de negócio da aplicação exija esse comportamento, pois a mesma aumenta o risco de perdas de dados.
ON {DELETE|UPDATE} NO ACTION é a restrição padrão quando não informamos nenhuma na criação da tabela com constraints ou em comandos Alter table add constraints. Se você tentar deletar um registro pai a qual tenha registros filhos referenciados ocorrerá um erro. Vamos a um exemplo.
Desejamos deletar um PRODUTO cadastrado em nossa base de dados, no entanto esse produto já foi vendido diversas vezes e por isso está referenciado na tabela ITEM_PEDIDO.
Verificando os produtos cadastrados.
SELECT * FROM GPO.PRODUTO;

Verificando os produtos que estão na tabela ITEM_PEDIDO.
SELECT * FROM GPO.ITEM_PEDIDO;

Notamos que existem dois registros para referenciando o ID_PRODUTO 1, vamos tentar apaga-lo e acontecera o erro ORA-02292 restrição de integridade violada, ou seja, não podemos deletar o produto 1 enquanto houver registros referenciando ele em outras tabelas.
Uma observação importante é que não é permitido informar o ON DELETE NO ACTION no momento da criação da Foreign Key, basta não mencionar nenhuma outra restrição que ela será criada automaticamente. Caso tente criar a foreign key indicando ON DELETE NO ACTION acontecerá um erro de sintaxe.
CREATE TABLE GPO.ITEM_PEDIDO ( ID_ITEM_PEDIDO NUMBER NOT NULL , ID_PEDIDO NUMBER NOT NULL , ID_PRODUTO NUMBER NOT NULL , QT_ITEM NUMBER NOT NULL , VL_TOTAL NUMBER(9,2) , CONSTRAINT PK_ITEM_PEDIDO PRIMARY KEY (ID_ITEM_PEDIDO) , CONSTRAINT FK_ITEM_PEDIDO_PEDIDO FOREIGN KEY (ID_PEDIDO) REFERENCES GPO.PEDIDO (ID_PEDIDO) ON DELETE CASCADE , CONSTRAINT FK_ITEM_PEDIDO_PRODUTO FOREIGN KEY (ID_PRODUTO) REFERENCES GPO.PRODUTO (ID_PRODUTO) ON DELETE NO ACTION );

Para criar a foreign key como ON DELECT NO ACTION basta não mencionar nenhuma outra restrição que ela será criada automaticamente.
CREATE TABLE GPO.ITEM_PEDIDO ( ID_ITEM_PEDIDO NUMBER NOT NULL , ID_PEDIDO NUMBER NOT NULL , ID_PRODUTO NUMBER NOT NULL , QT_ITEM NUMBER NOT NULL , VL_TOTAL NUMBER(9,2) , CONSTRAINT PK_ITEM_PEDIDO PRIMARY KEY (ID_ITEM_PEDIDO) , CONSTRAINT FK_ITEM_PEDIDO_PEDIDO FOREIGN KEY (ID_PEDIDO) REFERENCES GPO.PEDIDO (ID_PEDIDO) ON DELETE CASCADE , CONSTRAINT FK_ITEM_PEDIDO_PRODUTO FOREIGN KEY (ID_PRODUTO) REFERENCES GPO.PRODUTO (ID_PRODUTO) );

Referências
Andrei Rubino, pós-graduado em Gerenciamento de Projetos e graduado em Análise e Desenvolvimento de sistemas, trabalha com banco de dados e soluções Oracle há 9 anos, atualmente atua como DBA na Cast Group alocado na Caixa Econômica Federal.