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
Advertisement
Answer
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.