Foreign Key Constraint in SQL

Foreign Key is a column/field that refers to the primary key/unique key of another table. So it demonstrates the relationship between tables and acts as a cross reference among them.

A foreign key is created in the CREATE TABLE or ALTER TABLE statement. The foreign key in a table should match the primary key in the referenced table for every row. This is called Referential Integrity. Foreign key ensures referential integrity.

The table in which a foreign key is defined is called a Foreign table/Child table/Referencing table. and the table that defines a primary key and is referenced by a foreign key is called a Primary table/Parent table /Referenced Table

Properties:

• The parent field that is being referenced has to be unique/Primary Key.
• The child field may have duplicates and nulls.
• Parent records can be deleted if no child exists.
• The master table cannot be updated if a child exists.
• Must reference PRIMARY KEY in the primary table.
• The foreign key column and constraint column should have matching data types.
• Records cannot be inserted in the child table if a corresponding record in the master table does not exist.
• Records of the master table cannot be deleted if corresponding records in the child table exist.

Syntax

There are two ways to add a foreign key to the table. We can add a foreign key at the column level or the table level.

SQL Foreign key At column level

CREATE TABLE people (no int references person, 
                          Fname varchar2(20));
                OR
CREATE TABLE people (no int references person(id), 
                              Fname varchar2(20));

Here Person table should have a primary key with type int. If there is a single columnar Primary key in the table, the column name in syntax can be omitted. So both the above syntax works correctly. To check the constraint,

If the Parent table doesn’t have a primary key.

OUTPUT:

Error at line 1 : referenced table does not have a primary key.

If the Parent table has the Primary Key of a different datatype.

OUTPUT:

Error at line 1 : column type incompatible with referenced column type.

SQL Foreign key At table level

CREATE TABLE people(no varchar2(10), 
                     fname varchar2(20), 
                     foreign key(no) references person);
                        OR
CREATE TABLE people(no varchar2(10), 
                     fname varchar2(20), 
                     foreign key(no) references person(id));

The column name of the referenced table can be ignored.

Insert Operation in Foreign Key Table

If a corresponding value in the foreign table doesn’t exist, a record in the child table cannot be inserted.

OUTPUT :

Error at line 1 : integrity constraint violated - parent key not found.

Delete Operation in Foreign Key Table

When a record in the master table is deleted and the corresponding record in the child table exists, an error message is displayed and prevents the DELETE operation from going through.

Error at line 1 : integrity constraint violated - child record found.

Foreign Key with ON DELETE CASCADE 

The default behavior of the foreign key can be changed using ON DELETE CASCADE. When this option is specified in the foreign key definition, if a record is deleted in the master table, all corresponding records in the detail table will be deleted. 

Syntax:

CREATE TABLE people (
   no varchar2(10),
   fname varchar2(20),
   foreign key(no) references 'person' on delete cascade
);

Now deleting records from the person table will delete all corresponding records from the child table.

OUTPUT:

SELECT * FROM person;
no rows selected 
SELECT * FROM people;
no rows selected 

Foreign Key with ON DELETE SET NULL 

A Foreign key with SET NULL ON DELETE means if the record in the parent table is deleted, corresponding records in the child table will have foreign key fields set to null. Records in the child table will not be deleted. 

Syntax:

CREATE TABLE 
people(no varchar2(10), 
                 fname varchar2(20), 
                    foreign key(no)
references person on delete set null);

OUTPUT :

SELECT* from person;
no rows selected

SELECT* from people;
NO Fname
pqr 

Notice the field “No” in people table that was referencing Primary key of Person table. On deleting person data, it will set null in child table people. But the record will not be deleted.