Foreign key

A foreign key is a column in a table that does NOT uniquely identify rows in that table, but is used as a link to matching columns in other tables to indicate a relationship.

For example, the emp.depto column is a foreign key pointing the the dept table's primary key - dept.deptno.

Examples

Inline and out of line definitions

Define a table with primary key to reference:

CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);

Inline foreign key (part of column definition):

CREATE TABLE t2 (
        c1 NUMBER PRIMARY KEY,
        c2 NUMBER REFERENCES t1(c1) );

Out-of-line foreign key (after column definitions):

CREATE TABLE t3 (
        c1 NUMBER,
        c2 NUMBER,
        CONSTRAINT t1_fk FOREIGN KEY (c1) REFERENCES t1);

ON DELETE CASCADE

Foreign key with ON DELETE CASCADE:

CREATE TABLE parent (id NUMBER PRIMARY KEY);
CREATE TABLE child  (id NUMBER PRIMARY KEY,
                    pid REFERENCES parent(id) ON DELETE CASCADE);

If rows are deleted from the parent table, referenced rows will automatically be removed from the child table.

ON DELETE SET NULL

Foreign key with ON DELETE SET NULL:

CREATE TABLE parent (id NUMBER PRIMARY KEY);
CREATE TABLE child  (id NUMBER PRIMARY KEY,
                    pid REFERENCES parent(id) ON DELETE SET NULL);

If rows are deleted from the parent table, referenced rows field will automatically be set to null in the child table.

ON UPDATE CASCADE

Although this is not available in Oracle Database but can be acheived by using Foreign Key Deferred Constraint as follows

Example: DEPT table having primary key on deptno and EMP table having foreign key on DEPT's deptno

create table emp (
  empno char(4),
  ename varchar2(10),
  deptno number(2),
  foreign key (deptno) references dept (deptno) initially deferred
);

Two Options:

1. Initially immediate(default) - constraint validated at statement level
2. Initially deferred - constraint validated at commit level

To get the table constraints and their attributes:

select table_name, 
       constraint_name, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED     
from user_constraints 
where table_name in ('EMP', 'DEPT');

Also see

• Constraint
• Primary key

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#