The FOREIGN KEY constraint is used to enforce referential integrity in the oracle database. The basic concept about FOREIGN KEY constraint is that “the values of a table (parent table) must store in another table (child table)”.
By the FOREIGN KEY constraint, we can build relationship between two or more tables.
- The referenced table is known as Parent Table. The parent table is defined by setting PRIMARY KEY constraint. So, the parent table is a table where PRIMARY KEY constraint has been set.
- The table where FOREIGN KEY constraint has been defined is called Child Table.
The FOREIGN KEY column always use reference of parent table column on which PRIMARY KEY constraint is set.
Finally we can say that -before inserting or updating data, the values of FOREIGN KEY column validates the value existence in the PRIMARY KEY column.
When you want to check before inserting or updating data in the child table that whether the values exist in the parent table or not, then you have to must use FOREIGN KEY constraint in the child table. If you attempt to insert or update in the child table by any value that are not in parent table, then the system will not perform the transaction and generate a system error.
Note : A FOREIGN KEY is used as a link with the matching column of parent table to build up a relationship between two or more tables. So, this FOREIGN KEY constraint also can be specified as Parent & Child relationship.
Let us examine the example below. Here we are focusing on two tables, one is REGISTRATION table which is parent table and another is MARKS table which is child table.
The records of REGISTRATION table:
|0521||Md. Abdul Gani||CSE||01711454648|
|0522||Md. Abul Hasan||CSA||01812546585|
|0533||Md. Jabbar Ali||MCSE||01745632352|
In the REGISTRATION table, REGISTRATIONNO column has been specified by PRIMARY KEY constraint. So, the REGISTRATION table is parent table also known as referenced table.
The records of MARKS table:
In the MARKS table, REGISTRATIONNO column has been defined as FOREIGN KEY constraint. So, the MARKS table is child table.
Here, a parent & child relationship has been built up between REGISTRATION (parent) and MARKS (child) table. So, FOREIGN KEY column of MARKS table points to the matching values of the PRIMARY KEY column of REGISTRATION table.
When you attempt to insert or update data in the REGISTRATIONNO column of MARKS table (child table), then FOREIGN KEY will validate the data whether that matching data is exist in the REGISTRATIONNO column of REGISTRATION table (parent table). If it finds the matching data in the REGISTRATIONNO column of parent table REGISTRATION, then data will be successfully inserted or updated in the REGISTRATIONNO column of child table MARKS. Otherwise the system will generate error and will not complete the transaction.
So, in conclusion we can say that FOREIGN KEY constraint prevents unmatched data (the data which is not exist in parent table)to be inserted or updated in the child table.
Note: The FOREIGN KEY constraint can be set by either CREATE TABLE statement or ALTER TABLE statement.
Syntax for CREATE TABLE statement:
CREATE TABLE table_name
column_name datatype(length) REFERENCES parent_table_name (primary_key_column_name)
REFERENCES keyword is used to point to the column of parent table.
parent_table_name is the name of referenced table (parent table) where PRIMARY KEY constraint is defined.
primary_key_column_name is the name of the column of parent table on which PRIMARY KEY has been set.
Syntax for ALTER TABLE statement:
ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column_name) REFERENCES parent_table_name (primary_key_column_name);
FOREIGN KEY keyword is used to define a column as FOREIGN KEY column or link column .
constraint_name is the name of FOREIGN KEY constraint can be started with fk_ .
child_table_name is the name of child table where FOREIGN KEY will be defined.
foreign_key_column_name is the name of column of child table on which FOREIGN KEY will be set.