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.
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 );
- Initially immediate(default) - constraint validated at statement level
- 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');
|Glossary of Terms|