3

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.
  • Check constraints cannot alter the values. They can restrict the insert or updating of rows if the constraint is violated. – ypercubeᵀᴹ Aug 27 '14 at 13:34
  • yeah but some where on internet I checked that we can use case statement by using End=1 is this possible? – AA.SC Aug 27 '14 at 13:36
  • What error do you get exactly? The constraint seems valid syntactically. And it is: fiddle. Either you have some other code that produces the error or you have already data in the table that violate the constraint (and the error is that the constraint cannot be added.) – ypercubeᵀᴹ Aug 27 '14 at 13:39
  • I updated my question as I successfully created constraint. – AA.SC Aug 27 '14 at 13:45
  • You may have successfully created the constraint, but as you can see from my answer below - it won't work as intended. You cannot update a row from inside a constraint, you can only validate if the inserted values fit the constraint requirements. – Max Vernon Aug 27 '14 at 14:23
5

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;
2

Don't you really want a computed column (as per your title), so you can preserve the original value, and calculate a new one? eg

CREATE TABLE dbo.tblTestCheckConstraint 
    (
    id INT PRIMARY KEY,
    NewColumn INT NOT NULL,
    ccNewColumn AS CASE WHEN NewColumn > 4 THEN 1 ELSE NewColumn END
    )

INSERT INTO dbo.tblTestCheckConstraint ( id, NewColumn )
VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )

enter image description here

  • Wbob, If I don't want to add an extra column to my table instead of trigger is good option but my question is if I can create successful check constraint with case statement then where is the problem – AA.SC Aug 27 '14 at 13:53
  • 1
    The reason your constraint is succeeding is because of the = 1 at the end of it. You're creating a check that evaluates to "when 1 equals 1, ok, else error". Check constraints evaluate to true or false, right? eg insert ( 1, 2 ) => case when 2 = 1 => false => error. eg insert ( 1, 5 ) => case when 1 = 1 => true => ok. Your check will always prevent the values 2,3,4 going into your column. Review check constraints here: technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx – wBob Aug 27 '14 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.