SQL Server Performance Forum – Threads Archive

Unique index and nulls

Hi,
I am adding a new column in our database called Order. This will generally be null, but in some situations, will have a value if we want things in our application to be done in a certain order. Order should be unique, but as there are multiple null values i can not add a unique index. I tried adding a check constraint something like ‘Order NOT IN (SELECT Order FROM tbl_Table WHERE Order IS NOT NULL)’ and got a message that check constraints cant have sub-queries. Any suggestions?
Thanks in advance
Ben

---

I see two solutions for that problem: 1. Use stored procedure to insert/update rows in the table and apply logic mentioned in sp code.
2. Use insert/update trigger on that table.

---

]]>