<< GROUP BY DataTable Method - A SQLOps method | Home | A few DBA Jokes - Lame factor high >>

Unique values, many NULLs - A Dodgy Constraint.

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...:-)

    Print | posted on Friday, May 21, 2004 10:47 AM

    Feedback

    # Take Outs for 20 May 2004

    left by Enjoy Every Sandwich at 5/21/2004 1:26 AM
    Take Outs for 20 May 2004

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by robvolk at 5/21/2004 11:18 PM
    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

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by DavidM at 5/22/2004 12:00 AM
    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?

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by SamS at 5/25/2004 4:10 AM
    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?

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by Brett at 5/26/2004 4:16 AM
    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?

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by DavidM at 5/26/2004 10:56 AM
    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..

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by robvolk at 5/27/2004 11:09 PM
    Sorry David, I goofed big time. I didn't realize the non-deterministic functions aren't allowed in this situation (which really sucks)

    # CREATE TYPE2 UNIQUE WHERE NOT NULL INDEX

    left by x002548's Blog at 4/20/2005 1:23 PM

    # CREATE TYPE2 UNIQUE WHERE NOT NULL INDEX

    left by x002548's Blog at 4/20/2005 1:24 PM

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by rnickel at 8/24/2005 9:24 AM
    <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?

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by alexr at 11/8/2005 8:37 AM
    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!

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by brent at 12/8/2005 1:56 AM
    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.

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by James at 6/21/2006 1:04 AM
    '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!

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by James at 6/21/2006 1:37 AM
    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.

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by Giannis Tsakiris at 8/24/2007 7:35 PM
    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.

    # re: Unique values, many NULLs - A Dodgy Constraint.

    left by Wes Clark at 6/13/2008 2:19 AM
    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.
    Title  
    Name
    Email (never displayed)
    Url
    Comments   
    Please add 4 and 2 and type the answer here:
    Copyright © byrmol