Home Why can't I add a column to an existing table with a checkConstraint that references other columns in SQL

Questions

Why can't I add a column to an existing table with a checkConstraint that references other columns in SQL

December 9, 2021

I’m using SQL Server and am trying to add a column and a check constraint. I’ve found that the following works:

ALTER TABLE table.column
    ADD isTrue BIT

GO
ALTER TABLE table.column
    ADD CONSTRAINT CK_table_isTrue CHECK ((isTrue = 1 AND column1 = 0 AND column2 = 0 AND column3 IS NULL) OR isTrue = 0)

However a less verbose way of writing this does not work:

ALTER TABLE table.column
    ADD isTrue BIT
    CONSTRAINT CK_table_isTrue CHECK ((isTrue = 1 AND column1 = 0 AND column2 = 0 AND column3 IS NULL) OR isTrue = 0)

The following error is output:

Column CHECK constraint for column ‘isTrue’ references another column, table ‘table’.

Looking at docs and SO I was unable to determine why this is the case

>Solution :

Your syntax is not quite right. A constraint that references multiple columns is a table constraint. Your’re trying to add a table constraint so you need a comma after the datatype definition for isTrue.

ALTER TABLE table.column
    ADD isTrue BIT,
    CONSTRAINT CK_table_isTrue CHECK ((isTrue = 1 AND column1 = 0 AND column2 = 0 AND column3 IS NULL) OR isTrue = 0);

Without the comma SQL Server thinks you’re trying to add a column constraint thus the error that you’re referencing a different column.