Adding a Check Constraint in SQL

Question

For below table,

How to add a Constraint in SQL to enforce a maximum of one AddressID has Primary = TRUE where EndDate = 12/31/9999 ?

Can there be a duplicate of PK 7 with AddressId=102, Primary=FALSE, EndDate = 12/31/9999? — Tah, May 08 '17 at 18:31
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , May 08 '17 at 19:42

score 0 · Answer 1 · answered May 08 '17 at 18:21

0

You want to create a unique constraint on the 3 columns: AddressID, Primary, EndDate.

ALTER TABLE [YourTableName]
ADD CONSTRAINT [UniqueConstraintName] UNIQUE (AddressID, Primary, EndDate);

answered May 08 '17 at 18:21

Tah

1,526
14
22

In that case I can not have multiple FALSE. I should be able to have multiple FALSE – user3749816 May 08 '17 at 18:23
Also, as I stated in my question, only for EndDate 12/31/9999 I should restrict it to having 1 TRUE – user3749816 May 08 '17 at 18:24
Please provide more examples what what values are acceptable in the table. – Tah May 08 '17 at 18:26
Updated the question with more values – user3749816 May 08 '17 at 18:29

score 0 · Answer 2 · answered May 08 '17 at 19:18

What you need here is a unique index, not a constraint; an index can be filtered, though a constraint cannot. Please try the index creation script below and let me know how it goes.
Another point, you have a field named PRIMARY which is a reserved word in SQL Server; while you are able to circumvent syntactical issues by enclosing the field name in square brackets (as I did in the index script below) you would want to try to avoid that naming convention if at all possible.

CREATE UNIQUE INDEX idx_max_address_id
ON myTable (PK, AddressId)
WHERE [PRIMARY] = 'TRUE'
AND EndDate = '12/31/9999'

Adding a Check Constraint in SQL

2 Answers2