«Back to Home

Oracle Jump Start
Topics

How To Use Foreign Key In Oracle

Foreign key in Oracle

A foreign key establishes and enforces a link between the data in the two tables. We can create a foreign key by defining a foreign key constraint, while creating or modifying/altering a table.
The referenced table is called the parent table, while the table with the foreign key is called the child table.

A foreign key can be defined in either a create table or an alter table statement.

Syntax

Using a create table statement
  1. Create table table_name  
  2. (  
  3. Column 1 datatype(),  
  4. Column 2 datatype(),  
  5. . .  
  6. . .  
  7. . .  
  8. Column n datatype()  
  9. CONSTRAINT fk_constraint_name  
  10. FOREIGN KEY (column1, column2, ... column_n)  
  11. REFERENCES parent_table (column1, column2, ... column_n)  
  12. );  
  13. Commit;  
Example
  1. create table department  
  2. (  
  3. dept_id number(20),  
  4. dept_name varchar2(14),  
  5. loc varchar2(15),  
  6. constraint pk_department primary key (dept_id)  
  7. );  
  8. Commit;  
1
  1. create table employee  
  2. (  
  3. dept_id number(20),  
  4. emp_id number(20),  
  5. emp_name varchar2(15),  
  6. emp_address varchar2(100),  
  7. emp_mob number(10),  
  8. constraint pk_employee primary key (emp_id),  
  9. constraint fk_dept_id foreign key (dept_id) references department (dept_id)  
  10. );  
2

Using alter table statement
 
Syntax
  1. Alter table table_name  
  2. Add constraint Constraint_name  
  3. Foreign key(Column 1, column 2……column n)  
  4. References parent_table(column 1, column 2….column n);  
Example
  1. Alter table employee  
  2. Add constraint FK_department  
  3. Foreign key(Dept_id)  
  4. Reference department(dept_id)  
3

Foreign key with cascade delete

A foreign key with cascade delete means, if a parent table record is deleted, the child table record will automatically be deleted in Oracle.

Syntax
  1. Create table table_name  
  2. (  
  3. Column 1 datatype(),  
  4. Column 2 datatype(),  
  5. . .  
  6. . .  
  7. Column n datatype(),  
  8. Constraint fk_constraint_name  
  9. Foreign key (column1, column2, ... column_n)  
  10. References parent_table (column1, column2, ... column_n)  
  11. ON DELETE CASCADE  
  12. );  
Example
  1. create table employee  
  2. (  
  3. dept_id number(20),  
  4. emp_id number(20),  
  5. emp_name varchar2(15),  
  6. emp_address varchar2(100),  
  7. emp_mob number(10),  
  8. constraint pk_employee primary key (emp_id),  
  9. constraint fk_dept_id foreign key (dept_id) references department (dept_id)  
  10. ON DELETE CASCADE  
  11. );  
4

Foreign key with set null on delete

A foreign key with set null on delete means the parent table is deleted and the child table will have the foreign key is set to null. The child table will not be deleted.

Syntax
  1. Create table table_name  
  2. (  
  3. Column 1 datatype(),  
  4. Column 2 datatype(),  
  5. . .  
  6. . .  
  7. Column n datatype(),  
  8. CONSTRAINT fk_constraint_name  
  9. Foreign key (column 1, column 2, ... column n)  
  10. References parent_table (column 1, column 2, ... column n)  
  11. ON DELETE SET NULL  
  12. );  
Example
  1. create table employee  
  2. (  
  3. dept_id number(20),  
  4. emp_id number(20),  
  5. emp_name varchar2(15),  
  6. emp_address varchar2(100),  
  7. emp_mob number(10),  
  8. constraint pk_employee primary key (emp_id),  
  9. constraint fk_dept_id foreign key (dept_id) references department (dept_id)  
  10. ON DELETE SET NULL  
  11. );  
5

Output
 
6 

Summary

Thus, we learnt, a foreign key establishes and enforces a link between the data in the two tables i.e. called Foreign key and we learnt, how to use foreign key in Oracle with the examples.