Foreign Key

FOREIGN KEY (Referential Integrity Constraint)
            To establish a parent-child or a master-detail relationship between two tables having a common column. We make use of referential integrity constraints. To implement this we should define the column in the parent table as a primary key and the same column in the child tables as a foreign key referring to the corresponding parent entry.
Concepts:-
Foreign Key:- A column or combination of columns included in the definition of referential integrity which would refer to a referenced key.
Referenced Key:- It is unique or a primary key which is defined on a column belonging to the parent table.
Chile Table:-  This table depends upon the values present in the referenced key of the parent table, which is referred by a foreign key.
Parent Table:- This table determines whether insertion or updation of data can be done in child table. This table would be referred by child ‘s table foreign key.
The table in which the foreign key is defined is called a foreign table or Details table . the table that defines the primary key and is referenced by the foreign key is called the Primary table or Master Table.
            The master table can be referenced in the foreign key definition by using the references adverb. If the name of the column is not specified by default Oracle references the primary key in the master table.
Insert Or Update operation in the foreign table:- the existence of a foreign key implies that the table with the foreign key is derived. A foreign key must have a corresponding primary key or unique key value in the master table.
Delete operation on the primary table:- Oracle Displays an error message if the user tries to delete a record in the master table when corresponding records exist in the detail table.
Principles of Foreign key:-

• Rejects an insert or update of a value, if a corresponding value does not currently exist in the master key table.
• If the on delete cascade option is set a delete operation in the master table will trigger the delete operation for corresponding records in the detail table.
• Rejects a delete for the master table if corresponding records in the detail table exist.
• Must reference a primary key or unique column in primary table.
• Will automatically reference the primary key of the master table if no column or group of columns is specified when creating the foreign key .
• Requires that the foreign key columns and the constraint columns have matching data types.
• May reference the same table named in the create table statement.

Syntax:- columnname datatype(size) REFERENCES tablename [(col-name)]
[ON DELETE CASCADE]   à Column level
FOREIGN KEY (col-name) REFERENCES tablename à Table level.

Ex:-     1) Create table master(eno number(5) primary key,na varchar2(20),
Sal number(5));
Insert into master values(&eno,’&na’,&sal);

2) Create table trans(eno number(5) references master,na varchar2(20),
                                    Sal number(5));
             Insert into trans values(100,’prasad’,3200);
             Insert into trans values(200,’prasad’,2100);

•             Insert into trans values(210,’raja’,1200);
•             Delete from master where eno=100;
• Create table master1(eno number(5) primary key,na varchar2(20),

                                                            Sal number(5));
Insert into master1 values(100,’vishnu’,1200);
Insert into master1 values(200,’rajesh’,2100);
Insert into master1 values(300,’rajani’,1100);

1. Create table trans1(eno number(5) references master1

            ON DELETE CASCADE,na varchar2(20),sal number(5));
Insert into trans1 values(100,’prasad’,3200);
Insert into trans1 values(200,’prasad’,2100);
Delete from master1 where eno=100;
Select * from master1;
Note :- The delete operation on master table will trigger the delete operation on trans1 table.becuase of on delete cascade option specified for trans1 table. Observe the trans1 table.

• Create table trans2(eno number(5),na varchar2(20),

             sal number(5),FOREIGN KEY (eno) REFERENCES master);
Insert into trans2 values(100,’prasad’,3200);
Insert into trans2 values(200,’prasad’,2100);

•             Insert into trans2 values(210,’raja’,1200);
•             Delete from master where eno=100;

 

For
Online Classes

Contact Us: +919885348743