1

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
hikarunoryoma
  • 63
  • 4
  • 1
    What does "does not work." mean? Do you get an error? If so what is the error? – Dale K Dec 08 '21 at 22:10
  • Will edit the question above! But the error is the following: ` Column CHECK constraint for column 'isTrue' references another column, table 'table'.` – hikarunoryoma Dec 08 '21 at 22:13

1 Answers1

2

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.

squillman
  • 13,363
  • 3
  • 41
  • 60