Hello forum,

I little while ago i had to create the following constraint: the rows in a certain table had to have null values (0 or more) or unique non null values. This rule had to be in the database (let's please not go into 'why in the database and not in the bo layer?').

I always believed that a unique constraint on that column, plus an allow null setting for that column, will accomplish this task, but i never did this type of constraint before. Well... it didn't. The table accepts a maximum of one row with null value for that column. The i began to mess around with the ansi_nulls setting, but i could still not get what i wanted. SQL BOL is not 100 percent clear in this matter.

Am I missing something here guys? Is a check constraint with a UDF the only solution?

Thanks!

Defiant.

Are you inserting an empty string

""



Sal

Unfortunately, for purposes of entries in a UNIQUE constraint or index, all NULLs are considered equal (this despite the fact that NULLs are not considered equal to one another everywhere else) (unless SET_ANSI_NULLS is set false to disable SQL-92 standard behavior). As you've seen, SET_ANSI_NULLS does not affect the behavior of NULLs in index/constraints. Thus, you can only have one row with a NULL value in a UNIQUE constraint or index.

I know of no way around this except via a CHECK constraint as you suggest.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

No Sal, I am not inserting an empty string.

Thanks Jeff Mason! Things are now clear to me.

defiant.