Foreign Key Part III / Cascading referential integrity constraint

In this blog, we’ll discuss about the Cascading referential integrity constraint.

When we are creating the Foreign key constraint references to a Primary/Unique key, by default the Cascading referential integrity constraint for the Foreign key is set to “NO ACTION“. [It means we cannot modify or delete the Primary/Unique key once it is references by a Foreign key.]

Cascading referential integrity constraint have 2 rules namely.

  1. ON UPDATE : What happens to the Foreign key column value “ON UPDATE” of the Primary/Unique key value which it is referencing.
  2. ON DELETE : What happens to the Foreign key column value “ON DELETE” of the Primary/Unique key value which it is referencing.

The above two rules can have any one of the below action types

  1. NO ACTION: This is the default behavior.It means Primary/Unique key delete/update statement will be ROLLBACK and no action will be taken or both Foreign & Primary/Unique key. It is applicable by default, so it’ll continue to work even if we didn’t specify it explicitly.
  2. CASCADE : In this case any delete/update query on Primary/Unique key value will be cascading same to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will also be deleted from the table automatically.
  3. SET NULL : In this case any delete/update query on Primary/Unique key value will be set NULL values to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to NULL automatically.
  4. SET DEFAULT : In this case any delete/update query on Primary/Unique key value will be set Default values (of the Foreign key column) to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to Default values (of the Foreign key column) automatically.

Examples

–Table A
create table Dep
(
DID int Primary key,
DepName varchar(50)
)

–Example1A: Table B with foreign key references to Table A with No ACTION (Default)
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID)
Name varchar(50),
Gender char(1),
)

–Example1B: Table B with foreign key references to Table A with No ACTION
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE NO ACTION
Name varchar(50),
Gender char(1),
)

–Example2: Table B with foreign key references to Table A with CASCADE
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE CASCADE
Name varchar(50),
Gender char(1),
)

–Example3: Table B with foreign key references to Table A with SET NULL
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET NULL
Name varchar(50),
Gender char(1),
)

–Example4: Table B with foreign key references to Table A with SET DEFAULT
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET DEFAULT
Name varchar(50),
Gender char(1),
)

For DELETE action just replace the word UPDATE in the foreign key references line with DELETE

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here to read about the previous part :– Foreign key Part II

Advertisements

One thought on “Foreign Key Part III / Cascading referential integrity constraint

  1. Pingback: Foreign Key Part II | DATABASE

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s