Unique Constraints in already existing columns

[jcrivera]

My scenario is the following: (SQL Svr 7.0)

I have a table that contains (among others) a column "MyColumn" that up to now it has been defined as NULL due to business rules.

So, although this column can indeed allow null values, however I would like to create a UNIQUE constraint to it, so that every value placed on it is unique, except <NULL> of course.

As far as I read on Books Online, that can supposedly be done. However, when I navigate into the table's design, then on Properties, I attempt to create it as a UNIQUE CONSTRAINT (not index), but the system doesn't allow it, remarking that there is a duplicate value, which is the <NULL>.

I also try it by means of the Query Analyzer, get the same error.

Could anyone help?

 

[tlbroadbent]

What you have seen is the expected behavior. SQL Server 7 only allows one null in columns with a unique constraint. I believe the SQL 7 documentation is incorrect or at least misleading on this point. SQL 2000 BOL clears the issue with this statement.

&quot;When you attach a unique constraint to a column allowing null values, you ensure that at most one row will have a null value in the constrained column.&quot; Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[jcrivera]

Yep, I noticed...

Nevertheless, what I did was to force the behavior like the one I was expecting by means of a trigger. It's working fine now.

Thanks.