Foreign Key in DBMS

Example to create a foreign key

Reference Table

CREATE TABLE CUSTOMERS1(
   ID   INT , 
  DEPT VARCHAR (20)     
   PRIMARY KEY (ID)
); 

Child Table

CREATE TABLE CUSTOMERS2(
   ID   INT ,            
   ADDRESS VARCHAR (20)     
   REFERENCES CUSTOMERS1(ID)
); 

CUSTOMERS1 table:

CUSTOMERS2 table:

 

• ID column in the customers1 table is used as a foreign key  in the customers2 table which means all the ID values in customers2 must exist in the customers1 table 
• An ID value that is not present in customers1 table is not allowed to be entered in the customers2 table ID column 
• ID column of the customers1 table contains values as 65 66 67 now ID column in customers2 table must contain only these values that is 65 66 67 ,if the user enters other than this values in the ID column of the customers2 table it will raise an  error because customers1 table id column is a foreign key in the customers2 table 
• Hence we can observe that a link is maintained between two tables that is if you want to enter any data in the foreign key column table then we must add the data in the primary key column of the parent table if it is not present 

 

Note: 

• The column or  list of the column that is used as foreign key in the present table must be a primary key in another table 
• The structure and data type of a PRIMARY KEY column of one table which used as a FOREIGN KEY in another table must be the same 
• The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

 

Why foreign key ?

• A foreign key is used to prevent activities that would destroy the link between tables 
• A foreign key prevents invalid  data being inserted into the foreign key column because it restricts the user to enter only those values that are present in the primary key of another table.