| Restrição FOREIGN KEY | |
Neste módulo vamos ver o seguinte:
O que é uma FOREIGN KEY e como a definir
A restrição FOREIGN KEY permite definir um relacionamento entre duas tabelas, sendo as respectivas regras mantidas e validadas pela base de dados.
Vamos começar por analisar o diagrama Entidade-Relacionamento que descreve as tabelas que estamos a considerar no nosso exemplo de trabalho:
Em primeiro lugar vamos considerar o relacionamento entre JOGADOR e EQUIPA. Uma equipa possui vários jogadores, sendo que cada jogador joga numa única equipa num determinado momento no tempo. Se adicionarmos a dimensão tempo ao nosso problema, então o jogador joga em várias equipas ao longo do tempo, o que dá ao relacionamento a cardinalidade N para N. Este conceito temporal introduz a noção de "contrato" entre jogador e equipa: um jogador é contratado por um período de tempo.
Em segundo lugar vamos considerar o relacionamento entre EQUIPA e JOGO. Um jogo envolve duas equipas: a visitante e a visitada. Isto traduz-se num duplo relacionamento entre as duas entidades: uma relação define a equipa da casa enquanto a outra define a equipa visitante.
O script apresentado abaixo implementa os relacionamentos descritos no ponto anterior:
create table EQUIPA (
ID_EQUIPA NUMBER(10,0) not null,
NOME VARCHAR2(100) constraint nn_equipa_nome not null,
constraint PK_EQUIPA primary key (ID_EQUIPA)
);
create table JOGADOR (
ID_JOGADOR NUMBER(10,0) not null,
NOME varchar2(100) not null,
POSICAO varchar2(100) not null,
BI VARCHAR(100),
constraint PK_JOGADOR primary key (ID_JOGADOR),
constraint UK_JOGADOR unique (BI)
);
create table JOGO (
EQUIPA_CASA NUMBER(10,0) not null,
EQUIPA_VISITANTE NUMBER(10,0) not null,
DATA DATE not null,
LOCAL VARCHAR2(100) default 'Estádio Alvalade XXI' not null,
RISCO_SEGURANCA VARCHAR(100) not null,
constraint PK_JOGO primary key (EQUIPA_CASA, EQUIPA_VISITANTE, DATA),
constraint FK_JOGO_REF_EQUIPA_CASA foreign key (EQUIPA_CASA)
references EQUIPA (ID_EQUIPA),
constraint FK_JOGO_REF_EQUIPA_VISITANTE foreign key (EQUIPA_VISITANTE)
references EQUIPA (ID_EQUIPA),
constraint CK_JOGO_01 check (risco_seguranca in ('Alto','Medio','Baixo') and equipa_casa != equipa_visitante)
);
create table CONTRATADO (
ID_JOGADOR NUMBER(10,0) not null,
ID_EQUIPA NUMBER(10,0) not null,
DATA_INICIO DATE not null,
DATA_FIM DATE not null,
constraint PK_CONTRATADO primary key (ID_JOGADOR, ID_EQUIPA, DATA_INICIO),
constraint FK_CONTRATA_REFERENCE_JOGADOR foreign key (ID_JOGADOR)
references JOGADOR (ID_JOGADOR),
constraint FK_CONTRATA_REFERENCE_EQUIPA foreign key (ID_EQUIPA)
references EQUIPA (ID_EQUIPA),
constraint CKT_CONTRATADO check (data_inicio < data_fim)
);
|
Como se pode verificar a restrição FK_CONTRATA_REFERENCE_JOGADOR define que a coluna ID_JOGADOR da tabela CONTRATADO referencia a coluna com o mesmo nome da tabela JOGADOR. Isto define a coluna ID_JOGADOR em CONTRATADO como uma FOREIGN KEY, o que em português se costuma traduzir por chave externa ou estrangeira. A partir daqui vamos designar a tabela JOGADOR como MASTER deste relacionamento e a tabela CONTRATADO como SLAVE. A criação desta restrição obriga a base de dados a executar um conjunto de validações:
A restrição FK_CONTRATA_REFERENCE_EQUIPA define a coluna ID_EQUIPA da tabela CONTRATADO como FOREIGN KEY da coluna com o mesmo nome na tabela EQUIPA o que activa as regras de integridade referencial entre essas duas colunas.
Na tabela JOGO temos duas FOREIGN KEYS: uma define a equipa da casa, enquanto a outra define a equipa visitante. As regras de integridade referencial garantem que um JOGO se realiza entre equipas que existem, ou seja, que estão presentes na tabela EQUIPA. Esta regra não garante que o jogo se realiza entre duas equipas diferentes, o que é assegurado por uma restrição CHECK.
Uma base de dados Oracle tem as seguintes regras na definição de FOREIGN KEYS:
A criação de restrição FOREIGN KEY pode ser feita usando sintaxe de coluna ou de tabela, como descrito no quadro abaixo:
| Tipo | Sintaxe |
| Coluna (só é abrangida uma coluna) |
CONSTRAINT nome REFERENCES tabela (coluna) |
| Tabela (são abrangidas várias colunas) |
, CONSTRAINT nome FOREIGN KEY (col1, col2,...)
REFERENCES tabela (coluna1, coluna2, ...); Notar que col1 e col2 são os nomes de colunas da tabela que está a ser criada ou alterada, enquanto coluna1 e coluna2 são referências a outra tabela (ou à própria tabela num outro contexto). |
O diagrama abaixo mostra um relacionamento que é possível definir entre as tabelas EMP e DEPT:
O query abaixo efectua uma consulta ao dicionário de dados para validar se o relacionamento entre EMP e DEPT foi efectivamente criado:
select *
from user_constraints
where table_name in ('EMP','DEPT');
|
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED ------------------------------ ------------------------------ --------------- ------------------------------ ---------------- ------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ------------------------- ------------------------------ ------------------------------ ------- -------------- HR SYS_C004034 C EMP "EMPNO" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 06.04.08 1 rows selected |
O resultado do query mostra que a única restrição existente nas duas tabelas é NOT NULL sobre a coluna EMPNO de EMP. Isto significa que não existe FOREIGN KEY em EMP e que se a tentarmos criar não temos nem UNIQUE nem PRIMARY KEY em DEPT para estabelecer a relação. Para criar o relacionamento teremos que fazer o seguinte:
O comando abaixo implementa os dois passos descritos acima:
alter table DEPT modify (DEPTNO constraint PK_DEPT primary key); alter table EMP modify (DEPTNO constraint fk_emp_ref_dept references DEPT(DEPTNO)); |
alter table dept succeeded. alter table emp succeeded. |
No comando anterior usámos sintaxe de coluna. Na sequência de comandos abaixo removemos a restrição criada no passo anterior e criamos outra equivalente, agora usando a sintaxe de tabela:
alter table emp drop constraint fk_emp_ref_dept; alter table emp add constraint fk_emp_ref_dept foreign key (deptno) references dept(deptno); |
alter table emp succeeded. alter table emp succeeded. |
O query abaixo confirma a criação do relacionamento:
select *
from user_constraints
where table_name in ('EMP','DEPT');
|
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED ------------------------------ ------------------------------ --------------- ------------------------------ ------------------- ------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ------------------------- ------------------------------ ------------------------------ ------- -------------- HR PK_DEPT P DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 06.07.03 HR PK_DEPT HR FK_EMP_REF_DEPT R EMP HR PK_DEPT NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 06.07.03 HR SYS_C004034 C EMP "EMPNO" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 06.04.08 3 rows selected |
Relacionamento de uma tabela com ela própria
Como foi visto nos queries hierárquicos e na junção de uma tabela com ela própria é possível definir um relacionamento entre uma tabela e ela própria. O diagrama abaixo mostra que um empregado pode ter um MANAGER (MGR), correspondendo este a outra linha da própria tabela EMP.
O relacionamento é criado entre as colunas MGR e EMPNO, mas obriga a considerar EMPNO como PRIMARY KEY da tabela EMP. Esta tabela pode ser vista em dois contextos: a tabela dos empregados e a tabela dos "managers". O relacionamento é definido entre os dois contextos, que afinal são a mesma tabela. Os dois comandos estão descritos no exemplo abaixo:
alter table EMP modify (EMPNO constraint PK_EMP primary key); alter table EMP modify (mgr constraint FK_EMP_REF_EMP references EMP(EMPNO)); |
alter table EMP succeeded. alter table EMP succeeded. |
Not null e diagrama entidade relacionamento
No relacionamento anterior um empregado pode não ter um "manager", como é o caso de KING. Isto traduz-se em termos de modelo pelo circulo no lado da cardinalidade 1 (zero ou um) e na implementação física pelo facto da coluna MGR suportar valores NULL. Se olharmos para o relacionamento entre EMP e DEPT vemos que o DEPTNO também suporta valores NULL, pelo que temos um circulo do lado de DEPT.
Se na coluna DEPTNO de EMP adicionarmos a restrição NOT NULL, como no comando abaixo, o modelo passará ser representado como na figura abaixo:
alter table EMP modify (DEPTNO constraint NN_EMP_DEPTNO not null); |
alter table EMP succeeded. |
Remoção em cascata
A opção ON DELETE CASCADE pode ser adiciona à restrição FOREIGN KEY e permite que, quando uma linha da tabela MASTER é removida, todas as linhas associadas na tabela SLAVE são apagadas em "cascata", mantendo a base de dados a integridade referencial. Pelo perigo que representa uma remoção em "cascata" esta opção deve ser usada com muito cuidado.
O comando abaixo remove a restrição FK_EMP_REF_DEPT e recria-a usando a opção ON DELETE CASCADE:
alter table EMP drop constraint fk_emp_ref_dept; alter table emp add constraint fk_emp_ref_dept foreign key (deptno) references dept(deptno) on delete cascade; |
Realizado por Turtle Learning ®. Última alteração em 2011-08-14
Topo