Sql-server – Check Constraint with Case Statement

check-constraintssql serversql-server-2008

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of data it shows an error message.

create table tblTestCheckConstraint 
(
id int,
NewColumn int
)


alter table tblTestCheckConstraint
add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
)

Insert Statement: 

insert into tblTestCheckConstraint values ( 1,5)
insert into tblTestCheckConstraint values ( 1,2)

Error Message: 

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName"
, table "dbo.tblTestCheckConstraint", column 'NewColumn'.
The statement has been terminated.

Best Answer

Check constraints can only be used to verify if the data falls within a given set of parameters. The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client.

To test this, I created your table, along with your proposed check constraint, and inserted several rows of test data:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

enter image description here

Results:

enter image description here

When attempting to insert 2 and 3 (or in fact anything other than 1 or >4) the constraint will return false, indicating to SQL Server to not perform the insert, and return the error messages seen above.

Here is an example of how you could accomplish this using a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint 
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

enter image description here

Ensure you remove the constraint from the table before trying to insert data, since it may conflict with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;
Related Question