×

Courses

Chapters

>
<

Aptitude

Foreign Key in DBMS

Foreign Key

Sometimes you need to maintain and restrict only Same data in a table that is exactly the same column data of another table, this purpose is served by using a foreign key

The foreign key constraint is a column or list of columns which points to the primary key column of another table

The main purpose of the foreign key is only those values are allowed in the present table that will match to the primary key column of another table.

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 ,            
   ADDRES VARCHAR (20)     
   REFERENCES CUSTOMERS1(ID)
);
CUSTOMERS1 table:
IDDEPT
65Dairy
66Snacks
67Snacks
CUSTOMERS2 table:
IDADDRESS
65Hyderabad
66Chennai
67Hyderabad

  • 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 

Why Foreign Key

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.

→
←