ORACLE – Integridade Referencial e suas Restrições

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

• Foreign Key