Different type of referential actions in foreign key

Foreign key is an important part of referential integrity. But we often confuse about the foreign key referential actions and their implementations. Here is a beautiful collections that help you to clear your doubts related to it.

Foreign keys are defined in the ANSI SQL Standard, through a FOREIGN KEY constraint. The syntax to add such a constraint to an existing table is defined  below. Omitting the column list in the REFERENCES clause implies that the foreign key shall reference the primary key of the referenced table.

ALTER TABLE <TABLE identifier>

      ADD [ CONSTRAINT <CONSTRAINT identifier> ]

      FOREIGN KEY ( <COLUMN expression> {, <COLUMN expression>}... )

      REFERENCES <TABLE identifier> [ ( <COLUMN expression> {, <COLUMN expression>}... ) ]

      [ ON UPDATE <referential action> ]

      [ ON DELETE <referential action> ]

Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.

CREATE TABLE TABLE_NAME (

   id    INTEGER  PRIMARY KEY,

   col2  CHARACTER VARYING(20),

   col3  INTEGER,

   ...

   FOREIGN KEY(col3)

      REFERENCES other_table(key_col) ON DELETE CASCADE,

   ... )

 

If the foreign key is a single column only, the column can be marked as such using the following syntax:

CREATE TABLE TABLE_NAME (

   id    INTEGER  PRIMARY KEY,

   col2  CHARACTER VARYING(20),

   col3  INTEGER REFERENCES other_table(column_name),

   ... )

 

Foreign keys can be defined with stored proc statement.

sp_foreignkey tabname, pktabname, col1 [, col2] ...  [, col8]

tabname : is the name of the table or view that contains the foreign key to be defined.

 pktabname : is the name of the table or view that has the primary key to which the foreign key applies. The primary key must already be defined.

col1 : is the name of the first column that makes up the foreign key. The foreign key must have at least one column and can have a maximum of eight columns.

Because the Database Management System enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. SQL Server specifies 5 different referential actions that shall take place in such occurrences:

  CASCADE

  RESTRICT

  NO ACTION

  SET NULL

  SET DEFAULT

 

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete.

Example Tables: Customer(customer_id, cname, caddress) and Order(customer_id, products, payment)

Customer is the master table and Order is the child table, where 'customer_id' is the foreign key in Order and represents the customer who placed the order. When a row of Customer is deleted, any Order row matching the deleted Customer's customer_id will also be deleted.

NOTE: In Microsoft SQL, a cascading delete to a self-referencing table is not allowed. You must either use a trigger, create a stored procedure, or handle the cascading delete from the calling application. An example of this is where a single table has an ID as identity and a ParentID with a relationship to ID in the same table.

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table.

Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated. This is different from RESTRICT, which assumes at the outset that the operation will violate the constraint. Using NO ACTION, the triggers or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

 

Posted By: MR. JOYDEEP DAS