FOREIGN KEY is the column where it references another table primary key column. The table which contain foreign key is known as child table. The table that references to another table is known as parent table or referenced table.

 CREATE TABLE childtable_name (
     column1,
     column2,
      ………………,
     column_n 
     [ CONSTRAINT foreignkey_name] required_column data type FOREIGN KEY
     REFERENCES parent table ( parenttable_column) 
     [ ON DELETE | ON UPDATE { NO ACTION | CASCADE | SET NULL | 
     SET DEFAULT} ]); 

• column1, column2 - Represents the name of the columns which include in table.
• required_column - Represents the name of the column on which we going to create foreign key.
• CASCADE – Child table data is deleted or updated when parent table was deleted or updated.
• NO ACTION – Child table has not performed any operation on matching data when parent table was deleted or updated.
• SET NULL – Matching child table data set to null values, when parent table was deleted or updated.
• SET DEFAULT – Matching child table data set to their Default values, when parent table was deleted or updated.

CREATE TABLE childtable_name ( 
          column1, 
          column2,             
           ………………, 
         column_n    
         [ CONSTRAINT foreignkey_name] FOREIGN KEY (required_column1, 
         required_column2) REFERENCES parent table ( parenttable_column1,
         parenttablecolumn2)); 

• column1,column2 - represents the name of the columns which include in table.
• required_column1, required_column2 - represents the name of the columns on which we going to create foreign key.

ALTER TABLE Child_table  ADD [ CONSTRAINT foreignkey_name]
FOREIGN KEY (childtable_column)
REFERENCES parent_table (parenttable_column); 

• foreignkey_name - represents the user defined name of the foreign key.
• childtable _column - represents the name of the existing child table column on which we going to create foreign key.

  ALTER TABLE child_table_name DROP CONSTRAINT foreignkey_name; 

• foreignkey_name - Represents the user defined name of the foreign key.

Scenario – Creating FOREIGN KEY while creating a table.

Requirement – Creating dept_id as FOREIGN KEY while creating department_details table.

Let us assume employee_details table is the parent table and should create dept_id as a primary key of the table like below -.

CREATE TABLE employee_details (
   emp_id INT NOT NULL,
   emp_name VARCHAR (20) NOT NULL,
   dept_id INT NOT NULL, 
   PRIMARY KEY (emp_id, dept_id)); 

The foreign key declaration query is as follows -

CREATE TABLE department_details (
       dept_id INT NOT NULL,
       dept_name varchar (20),
       dept_location varchar (20), 
       CONSTRAINT fk_employee FOREIGN KEY (dept_id)
       REFERENCES employee_details (dept_id)); 

By executing above query, dept_id foreign key was created on department_details table.

Scenario – Creating FOREIGN KEY with multiple columns while creating a table.

Requirement – Creating employee foreign key with dept_id, dept_name columns while creating department table.

Let us assume employee_details table is the parent table and should create dept_id as a primary key of the table like below -.

CREATE TABLE employee_details (
      emp_id INT NOT NULL,
      emp_name VARCHAR (20) NOT NULL,
      dept_id INT NOT NULL,
      dept_name VARCHAR (20) NOT NULL,
      PRIMARY KEY (emp_id, dept_id, dept_name)); 

The foreign key declaration query is as follows -

CREATE TABLE department_details (
       dept_id INT NOT NULL,
       dept_name VARCHAR (20),
       dept_location VARCHAR (20),
       CONSTRAINT fk_employee FOREIGN KEY (dept_id, dept_name) 
       REFERENCES employee_details (dept_id, dept_name)); 

By executing above query, employee foreign key was created on department_details table with dept_id, dept_name columns.

Scenario – Creating FOREIGN KEY on existing table.

Requirement – Creating dept_id as foreign key on existing department_details table. The query was as follows –

let us consider department table was created before as below -

CREATE TABLE department_details ( 
        dept_id INT NOT NULL,
        dept_name VARCHAR (20),
        dept_location VARCHAR (20)); 

The query to add foreign key s follows –

ALTER TABLE department_details
     ADD CONSTRAINT fk_employee
     FOREIGN KEY (dept_id)
     REFERENCES employee_details (dept_id); 

By executing above query, we can create foreign key on existing department_details table.

Scenario – Deleting FOREIGN KEY on existing table.

Requirement – Deleting fk_employee FOREIGN KEY on existing department_details table. The query was as follows –

 ALTER TABLE department_details DROP CONSTRAINT fk_employee; 

By executing above query, we can delete foreign key on existing department table.