SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. On whichever column you put FOREIGN KEY constraint then the values in that column must refer to existing values in the other table.

A foreign key column can refer to primary key or unique key column of other tables. This Primary key and foreign key relationship is also known as PARENT-CHILD relationship i.e. the table which has Primary Key is known as PARENT table and the table which has foreign key is known as CHILD table. This relationship is also known as REFERENTIAL INTEGRITY.

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don’t exist in the referenced table (except potentially NULL).

A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table’s primary key values to the other table.

This column becomes a foreign key in the second table.

The foreign key constraint can be imposed on column level or table level. If foreign key constraint uses more than one column then it must be defined on table level.

The following shows an example of parent child relationship:

PARENT TABLE: 

SQL> Create table EMP(Empno number (6) primary key, ename varchar2 (15), salary number (6), Comm number (5), deptno number (5), idno number (6));

Table created

CHILD TABLE:

SQL> create table attendance(Month varchar2 (9), days number (5), Empno number (6) constraint fk_attendance references EMP (empno));

Table created.

FOR TABLE LEVEL:

SQL>Create table attendance (month varchar2(7). days number(6). empno number(6), constraint fk_attendance foreign key(empno) references EMP (empno));

Table created.

Here EMPNO in attendance table is a foreign key referring to EMPNO of EMP table.

Some points to remember for referential integrity:

• You cannot delete a parent record if any existing child record is there. If you have to first delete the child record before deleting the parent record. In the above example you cannot delete row of employee no. 101 since its child exist in the ATTENDANCE table. However, you can delete the row of employee no. 103 since no child record exists for this employee in ATTENDANCE table. If you define the FOREIGN KEY with ON DELETE CASCADE option then you can delete the parent record and if any child record exists it will be automatically deleted.

• A composite foreign key must reference a composite primary key with the same number of columns and data types .

• The number of columns for the foreign key must match the:;number of columns of primary key.