FOREIGN KEY Constraint clauses

Here in this article I am not going to provide the definition of foreign key and how it works. As we all know very well about it.  

I am going to illustrate about FOREIGN KEY Constraint clauses. The FOREIGN KEY has two types of clauses mentioned bellow.

1.     ON DELETE

2.     ON UPDATE

Foreign key constraints may be created by referencing a primary or unique key. Foreign key constraints ensure the relational integrity of data in associated tables. A foreign key value may be NULL and indicates a particular record has no parent record. But if a value exists, then it is bound to have an associated value in a parent table. When applying update or delete operations on parent tables there may be different requirements about the effect on associated values in child tables. There are four available options in SQL Server 2005 and 2008 as follows:

     1.     NO ACTION

2. CASCADE
3. SET NULL
4. SET DEFAULT

Specification	Update operation on parent table	Delete operation on parent table
NO ACTION	Error message would be generated.	Error message would be generated.
CASCADE	Associated values in child table would also be updated.	Associated records in child table would also be deleted.
SET NULL	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.
SET DEFAULT	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and update operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and delete operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.

I am going to demonstrate it by a simple example.

Step-1 [ Create the Base Table and Insert some records in Master Table ]

IF OBJECT_ID('my_TranTbl') IS NOT NULL

   BEGIN

     DROP TABLE my_TranTbl

   END

GO

IF OBJECT_ID('my_MasterTbl') IS NOT NULL

   BEGIN

     DROP TABLE my_MasterTbl

   END

GO  

CREATE TABLE my_MasterTbl

       (IDNO      INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EMPNAME VARCHAR(50) NOT NULL)     

 

GO       

INSERT INTO my_MasterTbl

       (EMPNAME)

VALUES ('Joydeep Das'),

       ('Sukamal Jana'),

       ('Sudip kr. Das'),

       ('Sangram jit'),

       ('Tuhin Kr. Shinha')

GO      

CREATE TABLE my_TranTbl

       (IDNO      INT           NULL UNIQUE,

        PURCDT  DATETIME      NULL DEFAULT GETDATE(),

        PURCAMT DECIMAL(20,2) NULL DEFAULT (100))

GO

Step-2 [ Now Create Transaction table with Foreign Key to Check NO ACTION ]

ALTER TABLE my_TranTbl

ADD CONSTRAINT  my_TranTbl_FK FOREIGN KEY(IDNO) 

REFERENCES my_MasterTbl(IDNO)

Step-3 [ Now try to take an entry in Transaction table where there is no reference records (IDNO) in master table for that ]

INSERT INTO  my_TranTbl

       (IDNO, PURCDT, PURCAMT)

VALUES(1, '06-27-2012', 12.56)  

GO

INSERT INTO  my_TranTbl

       (IDNO, PURCDT, PURCAMT)

VALUES(2, '06-27-2012', 67.00)  

GO

INSERT INTO  my_TranTbl

       (IDNO, PURCDT, PURCAMT)

VALUES(3, '06-27-2012', 126.34)  

GO

-- Give error message as the Master not exists

INSERT INTO  my_TranTbl

       (IDNO, PURCDT, PURCAMT)

VALUES(7, '06-27-2012', 88.56) 

GO

It gives an error Message

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the FOREIGN KEY constraint "my_TranTbl_FK". The conflict occurred in database "ARTEK", table "dbo.my_MasterTbl", column 'IDNO'.

The statement has been terminated.

Step-4 [ To Check The CASCADE we need to Alter the definition of FOREIGN KEY ]

-- Drop the Foreign Key

ALTER TABLE my_TranTbl

DROP CONSTRAINT  my_TranTbl_FK

GO

ALTER TABLE my_TranTbl

ADD CONSTRAINT  my_TranTbl_FK FOREIGN KEY(IDNO) 

REFERENCES my_MasterTbl(IDNO)

ON DELETE CASCADE ON UPDATE CASCADE

GO

Now we are going to DELETE a record from master Table.

SELECT * FROM my_TranTbl

GO

Output:

IDNO  PURCDT                  PURCAMT

1     2012-06-27 00:00:00.000 12.56

2     2012-06-27 00:00:00.000 67.00

3     2012-06-27 00:00:00.000 126.34

DELETE my_MasterTbl

WHERE IDNO=1

GO

SELECT * FROM my_TranTbl

Output:

IDNO  PURCDT                  PURCAMT

2     2012-06-27 00:00:00.000 67.00

3     2012-06-27 00:00:00.000 126.34

Please look at the output carefully, here we delete the IDNO = 1 from master table as the ON DELETE CASCADE is specified in the FOREIGN KEY, it automatically delete the IDNO, 1 from Transaction table.

Step-5 [ To Check The SET NULL we need to Alter the definition of FOREIGN KEY ]

First we restore all the records.

ALTER TABLE my_TranTbl

ADD CONSTRAINT  my_TranTbl_FK FOREIGN KEY(IDNO) 

REFERENCES my_MasterTbl(IDNO)

ON DELETE SET NULL ON UPDATE SET NULL

Now we are going to delete the IDNO, 1 from Master Table.

DELETE my_MasterTbl

WHERE IDNO=1

GO

SELECT * FROM my_TranTbl

Output:

IDNO  PURCDT                  PURCAMT

NULL  2012-06-27 00:00:00.000 12.56

2     2012-06-27 00:00:00.000 67.00

3     2012-06-27 00:00:00.000 126.34

Please look at the output carefully, here we delete the IDNO = 1 from master table as the ON DELETE SET NULL is specified in the FOREIGN KEY, it automatically update the IDNO as NULL value in the Transaction Table.

Please note that the Transaction table columns must support the NULL Values.

 Step-6 [ To Check The SET DEFAULT we need to Alter the definition of FOREIGN KEY ]

 First we restore all the records.

Just make some changes in transaction table definition and used a DEFAULT CONSTRAINT.

CREATE TABLE my_TranTbl

       (IDNO      INT         NULL UNIQUE DEFAULT (100),

        PURCDT  DATETIME      NULL DEFAULT GETDATE(),

        PURCAMT DECIMAL(20,2) NULL DEFAULT (100))

GO

Insert a new Value in master table to maintain the referential integrity with mater-transaction when record is deleted from master table.

INSERT INTO my_MasterTbl

       (IDNO,  EMPNAME)

VALUES (100, 'Testing-Default') 

    

ALTER TABLE my_TranTbl

ADD CONSTRAINT  my_TranTbl_FK FOREIGN KEY(IDNO) 

REFERENCES my_MasterTbl(IDNO)

ON DELETE SET DEFAULT ON UPDATE SET DEFAULT

SELECT * FROM my_TranTbl

Output:

IDNO  PURCDT                  PURCAMT

100   2012-06-27 00:00:00.000 12.56

2     2012-06-27 00:00:00.000 67.00

3     2012-06-27 00:00:00.000 126.34

Here the DEFAULT value is replaced in the transaction table IDNO columns when the records is deleted from master table (IDNO = 1)

Hope you like it.

Posted by: MR. JOYDEEP DAS