SQL - Structured Query Language

Foreign Key Constraint

Foreign Key is used to define Referential Integrity Constraint with one column or set of columns that reference a Referenced Key in the Relational Database. Referenced Key is used to define Referential Integrity Constraint with Unique or Primary Key that referenced by Foreign Key in the Relational Database.
Foreign Key must define in the Child Table and Unique or Primary Key must define in the Parent Table. An instance, EMP is a Child Table that referenced DEPT Parent Table through common Dept_Code column. Relational Database Model authorizes to match the Foreign Key Data with Referenced Primary or Unique or NULL Data.
Referential Integrity Constraints can define with set of actions that execute automatically when modify the Referenced Parent Key Data. Most of the RDBMS support UPDATE and DELETE NO ACTION, ON DELETE CASCADE and SET NULL options. These actions must define in the Child Table.
UPDATE and DELETE NO ACTION is used to avoid update or delete referenced records in the Parent Table. ON DELETE CASCADE is used to delete referenced records in the Child Table when delete a referenced records in the Parent Table. SET NULL is used to update referenced records in the Child Table with NULL value when delete a referenced records in the Parent Table.
The following SQL Statements are used to create DEPT and EMP Tables with set of constraints.

-- Sample Statements --

CREATE TABLE DEPT

(

Dept_Code numeric(5) Primary Key,

Dept_Name varchar(50) Not Null,

)

CREATE TABLE EMP

(

Emp_Code numeric(5)Primary Key,

Emp_Name varchar(50) Not Null,

DOJ datetime Default GetDate(),

Salary numeric(7,2) Check (salary > 1000 and Salary < 5000),

Phone varchar(50),

Dept_Code numeric(5) References DEPT(Dept_Code)

On Delete Cascade

)

More Reference URL: https://data-e-education.com/RDBMS/Referential_Integrity_Constraints.php

* * * * *

Your Comment To AUTHOR