Foreign Key constraint in SQL

Foreign Key is a column that refers to the primary key/unique key of other table. So it demonstrates relationship between tables and act as cross reference among them. Table in which foreign key is defined is called Foreign table/Referencing table. Table that defines primary/unique key and is referenced by foreign key is called primary table/master table/ Referenced Table. It is Defined in Create table/Alter table statement.

For the table that contains Foreign key, it should match the primary key in referenced table for every row. This is called Referential Integrity. Foreign key ensures referential integrity.

Properties :

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

1. SQL Foreign key At column level :

   Syntax –

   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 primary key with type int. If there is single columnar Primary key in table, column name in syntax can be omitted. So both the above syntax works correctly.

   To check the constraint,

   • If Parent table doesn’t have primary key.

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

   • If Parent table has Primary Key of different datatype.

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

2. SQL Foreign key At table level :

   Syntax –

   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));
   

   Column name of referenced table can be ignored.

3. Insert Operation in Foreign Key Table :
   If corresponding value in foreign table doesn’t exists, a record in child table cannot be inserted.

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

4. Delete Operation in Foreign Key Table :
   When a record in master table is deleted and corresponding record in child table exists, an error message is displayed and prevents delete operation from going through.

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

5. Foreign Key with ON DELETE CASCADE :
   The default behavior of foreign key can be changed using ON DELETE CASCADE. When this option is specified in foreign key definition, if a record is deleted in master table, all corresponding record in 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 person will delete all corresponding records from child table.

   OUTPUT :
   select * from person;
   no rows selected 
   
   select * from people;
   no rows selected 

6. Foreign Key with ON DELETE SET NULL :
   A Foreign key with SET NULL ON DELETE means if record in parent table is deleted, corresponding records in child table will have foreign key fields set to null. Records in 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.