Unique values, many NULLs - A Dodgy Constraint.

Fri May 21, 2004 by byrmol

I have seen several times people requesting to place a unique constraint on a column that should allow many NULLs but only allow unique values.

Personally, I have never had to implement such a constraint, and when I see the question posed I assume it is needed because of a underlying design problem.

This only works for SQL 2K (as you will see in a minute) and requires various SET commands to be in place both an DDL and DML time…

The following solution places a UNIQUE constraint on a Computed column and according to BOL has restrict requirements…

The ANSI_NULL connection-level option must be set to ON when the CREATE TABLE statement is executed. The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.
These options must be set to ON:

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

In addition to these ON settings, the NUMERIC_ROUNDABORT option must be set to OFF

Assume that the column DC is the target of this dodgy constraint…

--DML
CREATE TABLE DodgyConstraint(DC VARCHAR(50) NULL, HelperColoumn INT identity(1,1) NOT NULL,
ComputedConstraint AS CASE WHEN DC IS NULL THEN CAST(HelperColoumn AS VARCHAR(50)) ELSE DC END, UNIQUE (ComputedConstraint))

–Insert 2 nulls
insert DodgyConstraint (DC) Values (NULL)
insert DodgyConstraint (DC) Values (NULL)
–Insert a couple of "real" value
insert DodgyConstraint (DC) Values ('Dave')
insert DodgyConstraint (DC) Values ('Dingo')
–Violation
insert DodgyConstraint (DC) Values ('Dave')

Select * from DodgyConstraint

One of the first things you would do if this type of constraint is needed is to project away the Identity column and the Computed column in a view and never speak of them again…:-)

Legacy Comments


robvolk 2004-05-21	re: Unique values, many NULLs - A Dodgy Constraint. David- Awesome! I have a curve ball situation though, if someone happens to store a number in the DC column, it would violate the constraint. But, I have a solution and it doesn't even require a helper column: CREATE TABLE DodgyConstraint(DC VARCHAR(50) NULL, ComputedConstraint AS CASE WHEN DC IS NULL THEN CAST(newid() AS VARCHAR(50)) ELSE DC END, UNIQUE (ComputedConstraint)) HTH

DavidM 2004-05-22	re: Unique values, many NULLs - A Dodgy Constraint. I tried that first Rob and couldn't get it to work, so I switched to the IDENTITY property.. The NEWID function is non-deterministic and so the UNIQUE constraint "should" fail. I can't get that to work on my box... Can you show me the SET statments that you are using?

SamS 2004-05-25	re: Unique values, many NULLs - A Dodgy Constraint. Hi, I have been thinking about this problem on and off for years now. Its the first time I've seen it solved using a computed column. The other solution I've seen is creating a single column view that only includes the not null values in DC. Then apply a unique index to the DC column of the view. As for whether its a dogdy constraint, I'm not really sure. In the example I've got currently, I have a Company table with columns: Id INT NOT NULL PRIMARY KEY ,Name VARCHAR(10) NOT NULL ,ExternalNumber INT NULL etc... Basically we create the company, name it and record some other information for it. Then we apply to a external body for a number for the company which they supply some time later. When they do supply a number it must be unique across all companies. I realise I could have a CompanyNumber table with a FK to the company table and a NOT NULL ExternalNumber field but the design seems a little clunky to me. Any thoughts?

Brett 2004-05-26	re: Unique values, many NULLs - A Dodgy Constraint. Yeah, I'm always amazed that DB2 has something SQL Server doesn't have.... But doesn't anyone else have a problem with messing around with setting? Is it only at CREATE TABLE time?

DavidM 2004-05-26	re: Unique values, many NULLs - A Dodgy Constraint. Brett, The only setting that you have to "mess" with is ARITHABORT.. The others are the SQL92 standard and are defaults for SQL Server Both at DDL and DML time is required. As usual BOL has the details..

robvolk 2004-05-27	re: Unique values, many NULLs - A Dodgy Constraint. Sorry David, I goofed big time. I didn't realize the non-deterministic functions aren't allowed in this situation (which really sucks)

rnickel 2005-08-24	re: Unique values, many NULLs - A Dodgy Constraint. <blockquote>When I see the question posed I assume it is needed because of a underlying design problem</blockquote> All right, Mr. Smarty David-- at my company we keep a list of customers. Most are from the US and have social security numbers. Some are foreign and have no social security numbers. If the person does have a social, we want to make sure it's not entered more than once. (For example, when someone tries to enter a customer again, not realizing they're already in the system.) For this, I would like to use the unique constraint that allows multiple nulls. What's my underlying design problem?

alexr 2005-11-08	re: Unique values, many NULLs - A Dodgy Constraint. re: SSN, null values and unique constraint Your design problem is that you should move the SSN column into a separate table with FK to your customers table to eliminate these null values. Nulls are the anathema of relational databases!

brent 2005-12-08	re: Unique values, many NULLs - A Dodgy Constraint. What does moving the SSN numbers to a separate table really solve? You still have NULL's in the foreign key in your original table and you still have the problem of enforcing uniqueness of only non-nulls in the referencing column. Then you are also adding a left outer join to all of your retreival code as well.

James 2006-06-21	re: Unique values, many NULLs - A Dodgy Constraint. 'Nulls are the anathema of relational databases!' True, and theoretically everything is as it should be. You do need an extra table to handle this situation properly. However this is a common situation and there really ought to be a workaround. Who really wants to add an extra table with one UNIQUE NOT NULL column just to solve this situation?? A pain in the neck!

James 2006-06-21	re: Unique values, many NULLs - A Dodgy Constraint. Sorry! I'd like to correct myself. This problem is a result of a rubbish implementation by Microsoft and does not agree with SQL standards. Microsoft will point out that they implemented unique constraints before nulls were allowed, but they should have forseen this situation. But the several workarounds I've seen include: separate table for the unique column, triggers, schemebinding view which ignores nulls and has a unique not null constraint on it. You don't use the view, but in effect it acts as a multi-null unique constraint.

Giannis Tsakiris 2007-08-24	re: Unique values, many NULLs - A Dodgy Constraint. Only microsoft would implement unique key constraints in such a peculiar fashion. null is not a value, null is the absence of value, only microsoft engineers could be so "smart" to consider that the absence of value can duplicate. (Obviously, they must implemented NULLs some sort of "special value", instead of no-value at all, and thus NULL fall to the constraint of the rest real value). Your workaround may be working and it's nice that you shared it, but I personally find it quite complicated... Thanks.

Wes Clark 2008-06-13	re: Unique values, many NULLs - A Dodgy Constraint. In SQL Server 2008, filtered indexes are being added that can eliminate the nulls, but it is not a perfect solution when there can be more than one column null. Oracle simple ignores them, and seems to work the best.