SqlTutorial-1 : Lesson-2 : Class-4 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Referential Integrity Constraint: Foreign Key with ON-UPDATE-DELETE Options

1: FOREIGN KEY

 "Foreign-Key is a column(s) of a table that act as primary key in another table"

• It enforces a containment relationship between two tables as one of them is called PARENT-Table and another one called CHILD-Table. The primary-key values of CHILD-Table must exist in Parent-Tables referenced column(s).
  • No direct insertion into Child unless you do not insert it into Parent.
  • No direct deletion from Parent unless you do not delete it from Child.

• FOREIGN KEY enforces Referential Integrity.
• The type and size of FORIENG KEY and REFERENCING table column must be same.
• Using ON DELETE and ON UPDATE option with FOREIGN key, you can configure the CHILD Table as changes in PARENT table automatically reflects to CHILD table.

Syntax:

[CONSTRAINT c_name] 
[ [ FOREIGN KEY] (col_name1 [{, col_name2} ...]]) 
       REFERENCES table_name (col_name3 [{, col_name4} ...]) 
          [ ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
          [ ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

Counter Examples

• e.g., For FOREIGN KEY as Table constraint.

  CONSTRAINT Fk_name FOREIGN KEY (emp_no) REFERENCES employee (emp_no)

• e.g, For FOREIGN KEY as Column constraints.

  emp_no INTEGER NOT NULL FOREIGN KEY REFERENCES Employee(emp_no)

Examples: Defining FORIEN KEY while table creation

1. FOREIGN KEY as table Constraints.

   // PARENT TABLE.
   CREATE TABLE employee (
       emp_no INTEGER NOT NULL PRIMARY KEY,
       emp_name CHAR(20) NOT NULL
       dept_no CHAR(4) NULL
   )
   //CHILD TABLE
   CREATE TABLE myProject (
       emp_no INTEGER NOT NULL,
       project_no CHAR(4) NOT NULL,
       job CHAR (15) NULL,
       CONSTRAINT Pk_works PRIMARY KEY (emp_no, project_no),
       CONSTRAINT Fk_works FOREIGN KEY (emp_no) 
           REFERENCES employee (emp_no) 
   )

2. FOREIGN KEY as Column Constraints.

   //CHILD TABLE
   CREATE TABLE myProject (
       emp_no INTEGER NOT NULL FOREIGN KEY REFERENCES Employee(emp_no),
       project_no CHAR(4) NOT NULL,
       job CHAR (15) NULL,
       CONSTRAINT Pk_works PRIMARY KEY (emp_no, project_no)
    )

3. Selecting IDENTITY column as FOREIGN KEY.

   //PARENT TABLE
   CREATE TABLE MyTable1 (
       EmpId1 Int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY
   )
   //CHILD TABLE
   CREATE TABLE MyTable2(
       EmpId2 int NOT NULL PRIMARY KEY,
       EmpName varchar,
       CONSTRAINT Fk_My FOREIGN KEY(EmpId2) 
           REFERENCES MyTable1(EmpId1) 
    )

Examples: Defining FORIEN KEY after table creation.

1. Example1: Adding F-Key Cosntraints

   ALTER TABLE MyTable2 WITH CHECK
       ADD FOREIGN KEY (EmpId2) REFERENCES MyTable1(EmpId1)

2. Example1: Adding F-Key Cosntraints

   ALTER TABLE Employees
       ADD CONSTRAINT Fk_MyTable1_MyTable2 
           FOREIGN KEY(EmpId2) REFERENCES Employees(EmpId1);

2: Extended Foreign-Key:
     ON UPATE and ON DELETE Options

For reflecting changes made to PARENT-Table to CHILD-Table or vice versa.

Consider a case, “What happened with corresponding records of CHILD table when PARENT table records modified/deleted”. Using ON DELETE and ON UPDATE option with FOREIGN key, you can configure the CHILD Table as changes made in PARENT table automatically reflects to CHILD table.

• NO ACTION (default): means no changes made to CHILD table.
• CASCADE: means same action performed to CHILD table as done on PARENT table.
• SET NULL: means record value of CHILD table set to NULL.
• SET DEFAULT: means record value of CHILD table set to DEFAULT (if available else error)

Syntax:

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

Examples:

1. Cascading Updates and Deletes

   Table1: PARENT TABLE
   CREATE TABLE Orders (
       OrderID int PRIMARY KEY ,
       CustomerID nchar (5) NULL ,
       OrderDate datetime NULL ,
       RequiredDate datetime NULL ,
       ShippedDate datetime NULL ,
       ShipVia int NULL ,
       …
   )
   Table2: CHILD TABLE
   CREATE TABLE OrderDetails (
       OrderID int NOT NULL,
       Description varchar(25) NOT NULL,
       UnitPrice money NOT NULL,
       Qty int NOT NULL,
       CONSTRAINT PKOrderDetails PRIMARY KEY (OrderID, PartNo),
       CONSTRAINT FKOrderContainsDetails FOREIGN KEY (OrderID)
           REFERENCES Orders(OrderID) ON UPDATE CASCADE 
                                           ON DELETE CASCADE )

2. How hierarchy is effected with ON DELETE | UPDATE with Cascade

   Table1:  PARENT1
   CREATE TABLE department(
       dept_no CHAR(4) NOT NULL,
       dept_name CHAR(25) NOT NULL,
       Location CHAR(30) NULL,
       CONSTRAINT prim_dept PRIMARY KEY (dept_no)
   )
   Table2:  PARENT2
   CREATE TABLE project (
       project_no CHAR(4) NOT NULL,
       project_name CHAR(15) NOT NULL,
       budget FLOAT NULL,
       CONSTRAINT prim_proj PRIMARY KEY (project_no)
   )
   Table3:  PARENT1_CHILD
   CREATE TABLE employee (
        emp_no INTEGER NOT NULL,
        emp_fname CHAR(20) NOT NULL,
        emp_lname CHAR(20) NOT NULL,
        dept_no CHAR(4) NULL,
        CONSTRAINT prim_emp PRIMARY KEY (emp_no),
        CONSTRAINT foreign_emp FOREIGN KEY(dept_no) 
           REFERENCES department(dept_no) 
   )
   Table4:  CHILD of PARENT2 and PARENT1_CHILD.
   CREATE TABLE myProject(
       emp_no INTEGER NOT NULL,
       project_no CHAR(4) NOT NULL,
       job CHAR (15) NULL,
       enter_date DATETIME NULL,
       CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
       CONSTRAINT foreign1_works1 
           FOREIGN KEY(emp_no) REFERENCES employee(emp_no) 
                   ON DELETE CASCADE,           
       CONSTRAINT foreign2_works1 
           FOREIGN KEY(project_no) REFERENCES project(project_no) 
                   ON UPDATE CASCADE
   )