Is Conditional Foreign Key is Possible

Introduction

One of my friends wants a conditional foreign key. I suppressed that is possible? Before proceed let’s see the case study.

Case Study

We have an Employee Master Table called tbl_EMPMASTER which contains designation for both team lead and programmer.

Now we are going to create two children Table and use foreign key with master table. We want the Team Lead (TL) designation holder stores in table called tbl_EMP_TL and the Programmer (PRG) designation holder stores on tbl_EMP_PRG.

We must use foreign key for referential integrity.

Here is the Pictorial Diagram of Case study

Solutions

/*

   Logical Conditional Foreign Key

*/

IF OBJECT_ID(N'dbo.tbl_EMPMASTER', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_EMPMASTER;

   END

GO

CREATE TABLE dbo.tbl_EMPMASTER

       (

         EMPID        INT         NOT NULL PRIMARY KEY,

         EMPNAME      VARCHAR(50) NOT NULL,

         EMPDESIG     VARCHAR(10) NOT NULL,

         EMPDSGSTAT   AS CASE WHEN EMPDESIG='TL'  THEN 1

                              WHEN EMPDESIG='PRG' THEN 2

                                     END PERSISTED,

             CONSTRAINT UK_EMPID_EMPDSGSTAT UNIQUE(EMPID, EMPDSGSTAT)

         );                         

-- Inserting Master Data

INSERT INTO dbo.tbl_EMPMASTER

       (EMPID, EMPNAME, EMPDESIG)

VALUES (1, 'Joydeep Das', 'TL'),

       (2, 'Sangram Jit Bhattacharya', 'PRG');      

                                            

GO

IF OBJECT_ID(N'dbo.tbl_EMP_TL', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_EMP_TL;

   END

GO

CREATE TABLE dbo.tbl_EMP_TL

       (EMPID     INT           NOT NULL PRIMARY KEY,

        EMPSAL    DECIMAL(19,2) NOT NULL,

        EMPTLSTAT INT CHECK(EMPTLSTAT = 1),

        FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCES dbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT));

       

-- Insering Data For TL Only

INSERT INTO  dbo.tbl_EMP_TL

       (EMPID, EMPSAL, EMPTLSTAT)

VALUES (1, 2000, 1);

(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_TL

       (EMPID, EMPSAL, EMPTLSTAT)

VALUES (2, 1000, 1);

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint

"FK__tbl_EMP_TL__1A9D589D". The conflict occurred

in database "TEST", table "dbo.tbl_EMPMASTER".

The statement has been terminated. 

GO

IF OBJECT_ID(N'dbo.tbl_EMP_PRG', N'U') IS NOT NULL

   BEGIN

      DROP TABLE dbo.tbl_EMP_PRG;

   END

GO

CREATE TABLE dbo.tbl_EMP_PRG

       (

         EMPID     INT           NOT NULL PRIMARY KEY,

         EMPSAL    DECIMAL(19,2) NOT NULL,

         EMPTLSTAT INT CHECK(EMPTLSTAT = 2),

         FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCES dbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT)

       );

GO

INSERT INTO  dbo.tbl_EMP_PRG

       (EMPID, EMPSAL, EMPTLSTAT)

VALUES (2, 1000, 2);

      

(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_PRG

       (EMPID, EMPSAL, EMPTLSTAT)

VALUES (1, 2000, 2);                 

       

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint

"FK__tbl_EMP_PRG__260F0B49". The conflict occurred

in database "TEST", table "dbo.tbl_EMPMASTER".

The statement has been terminated. 

     

Hope you like it.

Posted by: Mr. JOYDEEP DAS