Table-level CHECK constraints

CHECK Constraint is used to maintain the domain integrity.

In this article I am not discussing related to the definition and syntax of the CHECK constraint, rather I am trying to illustrate a scenario where we can use the check constraint called Table-level CHECK Constraint.

Scenario

Table Object Name: my_TabEmpMASTER

Attributes Name	Description
EmpID	The Primary Key
EmpName	The Name of the Employee
TargetLevel	5000, 3000, 2000 etc

Table Object Name: my_TabEmpRecTarget

Attributes Name	Description
EmpID	The Primary Key/ Foreign Key reference with [my_TabEmpMASTER]
ActualTaget	The Actual Target

Now, we need to insert the record in table object named "my_TabEmpRecTarget", depends on Target Level. For example, if the Employee ID numbers comes on Target Level 5000, it should takes entry greater than or equals to 5000, not less than the 5000 and so on.

Solutions

The solutions can be easily achieved by using triggers. But being performance factors in mind we are going to achieved this by using CHECK constraints.

Let's start it by using Table-Valued CHECK Constraint

CREATE TABLE my_TabEmpMASTER

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

        EmpName       VARCHAR(50)  NOT NULL,

        TargetLevel     DECIMAL(4,0) NOT NULL)

GO

INSERT INTO my_TabEmpMASTER

           (EmpName, TargetLevel)

VALUES ('Joydeep Das', 5000),

       ('Sukamal Jana', 3000),

       ('Sangram Jit', 1000)                   

GO

SELECT * FROM my_TabEmpMASTER

GO

-- Result Set

EmpID       EmpName           TargetLevel

1           Joydeep Das       5000

2           Sukamal Jana      3000

3           Sangram Jit       1000

/*

  Now Create a UDF which can return the actual

  target level of an employee. It can take the EmpID as

  Parameters and return TargetLevel

 

*/

CREATE FUNCTION dbo.fnGetTargetLevel

      (

         @p_EmpID  INT

      )

RETURNS DECIMAL(4,0)

AS

BEGIN

   DECLARE @v_TRGLVL DECIMAL(4,0)

  

   SELECT @v_TRGLVL = [TargetLevel]

   FROM   my_TabEmpMASTER

   WHERE  EmpID = @p_EmpID

  

   RETURN @v_TRGLVL

END

GO

-- Now create the Transaction Table.

CREATE TABLE my_TabEmpRecTarget

       (EmpID         INT          NOT NULL PRIMARY KEY,

        ActualTaget     DECIMAL(4,0) NOT NULL)

GO

       

-- Now adding foreign key references

ALTER TABLE  my_TabEmpRecTarget

ADD CONSTRAINT my_TabEmpRecTarget_FK

FOREIGN KEY(EmpID) REFERENCES my_TabEmpMASTER(EmpID)

GO

-- Now Adding CHECK Constraint with Transaction Table

ALTER TABLE my_TabEmpRecTarget WITH NOCHECK

ADD CONSTRAINT chk_my_TabEmpRecTarget

CHECK (ActualTaget >= dbo.fnGetTargetLevel(EmpID))

-- Now we are Tring to Insert record

-- Inserting Record for Emp ID=1 'Joydeep Das'

-- With Actual Taget=6000 It is Not Violate the

-- CHECK Constraint as Target Level=5000, so the

-- Actual target is gretter then the Target Level

INSERT INTO my_TabEmpRecTarget

           (EmpID, ActualTaget)

VALUES     (1, 6000)          

-- Result Set

(1 row(s) affected)

-- Now we are Violating the CHECK constraint by

-- Inserting Actual Target=1000

-- for Emp ID=2 'Sukamal Jana' whos Target Level=3000

-- Here the Actual Target is less than the Target Level.

-- So violating the CHECK Constraint

INSERT INTO my_TabEmpRecTarget

           (EmpID, ActualTaget)

VALUES     (2, 1000)

-- Result Set

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_my_TabEmpRecTarget".

The conflict occurred in database "MYDB", table "dbo.my_TabEmpRecTarget".

The statement has been terminated.

 Don't worry about the Existing record, if you have the existing records in the transaction table named "my_TabEmpRecTarget", as we are using the "NOCHECK" switch.  SQL Server does not complain about the existing rows that don't pass the check, since we decided to use the NOCHECK switch.

Hope you like it.

Posted by: JOYDEEP DAS