Search

Change UNIQUE constraint to allow multiple NULL values by Hugo Kornelis

Active

168
13
Sign in
 to vote
Type: Suggestion
ID: 299229
Opened: 9/22/2007 1:31:50 PM
Access Restriction: Public
Duplicates: 498009
4
Workaround(s)
According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. SQL Server has always implemented a crippled version of this, allowing a single NULL but disallowing multiple NULL values.

Please start moving from the current non-standard implementation toward ANSI-standard behaviour for UNIQUE constraints.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

I am fully aware of the danger of breaking working code. This is a change that can't be made at once (unfortunately). A good upgrade path would be as follows:

1. (Preferably in SQL Server 2011): Introduce a new option for UNIQUE constraint definition: ALTER TABLE xxx ADD CONSTRAINT yyy UNIQUE (Col1, Col2) WITH ANSI_NULLS = [ON | OFF]. Make ANSI_NULLS = OFF (currect implementation of UNIQUE constraints) the default, so that existing code will continue to run unmodified.

2. One version later (SQL Server 2014?): Change the default for new UNIQUE constraint to ANSI_NULLS = ON (the ANSI specified behaviour where multiple NULLs are allowed).

3. Yet another version later (2017??): Mark the WITH ANSI_NULLS = [ON | OFF] option as deprecated.

4. (2020????): Remove the option.

*** Note that I edited the original suggested timeframe, since starting step 1 in SQL Server 2008 is no longer possible ***

Benefits

Faster Development
Improved Reliability
Other (please provides details below)

Other Benefits

Improved portability, adherence to industry standards
File Attachments
0 attachments
Sign in to post a comment.
Posted by Kanalpiroge on 9/27/2011 at 7:14 AM
I totally agree! Please implement the standard!!!
Posted by Shanus371 on 8/19/2011 at 12:14 PM
MS should implement the standard. Agreed 100%. For those saying it doesn't make sense, it only seems to show the lack of understanding of what NULL is. It's not string.Empty folks. NULL is nothing, not allocated, NULL does not equal NULL , NULL is not something. As being nothing means it cannot be compared to anything, even nothing, any field with a unique constraint and allow nulls should allow an unlimited number of fields to be NULL. That is proper behavior and it's not even really something up for debate. That is the way it should work.
Posted by Oliver Wilcock on 7/12/2011 at 5:23 PM
MS should implement the standard and quit wasting my time.
Posted by David Portas on 4/11/2011 at 2:21 AM
Added an alternative suggestion here: http://connect.microsoft.com/SQLServer/feedback/details/658638/dont-allow-nullable-columns-as-part-of-unique-constraints
Posted by Tom Thomson on 4/7/2011 at 12:08 PM
I think this is an appalling idea. We should instead be working to get the standards organisations to ghange the standard so that a UNIQUE column (or any column in a group of columns which together are unique) does not permit null, and planning to change SQL server to have the option to forbid NULL in any such column instead of allowing a single null.
Posted by Hugo Kornelis on 12/2/2010 at 2:44 AM
I have edited the proposed solution. Implementing the first step in SQL Server 2008, as originally suggested, is of course no longer possible. But I hope it's still possible to add this in SQL Server 2011.
Posted by Stan Segers on 7/21/2009 at 3:50 PM
Quoting SQL Server 2008 Books Online, SET ANSI_NULLS (Transact-SQL) :

[start quote]
Important:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
[end quote]

Does future mean next and does the NULL in a UNIQUE index mean NULL as in ANSI_NULLS?

Anyway, Hugo's proposed solution is one I can greatly accept. It doesn't break old code in this version and it hardens new code for the future... can we get it into the list of fixes for SQL Server 2005 SP4, SQL Server 2008 SP2 and the product release for Kilimanjaro/SQL Server 2008 R2?
Posted by DMulvena on 7/17/2008 at 2:12 AM
I agree that Microsoft SQL should follow the standard. NULL is a concept rather than a fixed value, and allowing only one row to have this, as if it were a fixed value, is illogical. (Equally illogical in my opinion is including all the records with null columns in an index - what is the point of that?) I can't agree with the suggestion though of overloading the existing ANSI_NULLS option.
Posted by Dragba on 6/12/2008 at 9:15 AM
I always had a problem with unique CONSTRAINT that allows multiple NULLs (or NULLs at all). However, I always missed unique INDEX that allows multiple NULLs. I'm pretty happy with MS solution (what took you so long?). Mind you that you can create a foreign key to the unique constraint. Doing that to the alternate key with multiple NULLs (or NULLs at all) makes no relational sense to me.
I understand the importance of standards, even when they are wrong, so MS could do something about it, keeping this behaviour default and recommended.
Posted by FluidTrade Support on 2/23/2008 at 10:00 AM
I need to add my voice to the list of people that think this is a big issue. The problem for me is less the compatibility with other databases than the compatibility with ADO.NET. The Unique constraints created for a DataSet allow multiple nulls but enforce uniqueness with the non-null values. I'm creating a middle tier where the in-memory functions have analogous functions on the persistent (read SQL) store. Currently, I can't create a constraint on SQL Server that works the same as the Unique constraint in my ADO.NET DataSet.
Posted by Hugo Kornelis on 1/29/2008 at 3:10 PM
You closed the suggestion as "fixed" after I posted my comment, but I felt compelled to re-open it. The suggestion was NOT to implement "some way" to constrain non-NULL values to be unique. The suggestion was to (gradually) change the UNIQUE keyword to behave as it is defined in the ANSI / ISO standards SQL-92, SQL:1999, SQL:2003, and probably some others as well.
Posted by Hugo Kornelis on 1/29/2008 at 10:49 AM
Erland: If you have indeed encountered situations where a UNIQUE constraint that permits a single NULL but not more is useful, then I'd prefer it if the non-standard constraint was given a proprietary name (eg UNIQUE_ONE_NULL_ONLY), and the name as defined in the standard (UNIQUE) behaves as described in the standard. That is, after all, what standardisation is all about!
Posted by Hugo Kornelis on 1/29/2008 at 10:47 AM
As glad as I am with the filtered index feature, I don't consider this a solution to my suggestion.

What I asked is to make UNIQUE behave as defined in the standard. The filtered index option is now presented as an argument for not having to come up to standards.

Every developer who comes from an ANSI-compliant platform gets bitten by the completely non-standard behaviour of UNIQUE with respect to NULL values. Standards are there for a reason. I have no objection what so ever against vendor-specific EXTENSIONS to the standard - but keywords that are defined in the standard, should behave as described in the standard. Otherwise, you might just as well throw all standardisation overboard and start advertising SQL Server as "the first major RDBMS that does not adhere to any standard".
Posted by Microsoft on 1/29/2008 at 10:05 AM
Great news! The new filtered indexes feature supports this, e.g.:

create unique nonclustered index idx on dbo.DimCustomer(emailAddress)
where EmailAddress is not null;
Posted by Erland Sommarskog on 12/10/2007 at 3:17 PM
Really bad idea. A new type of constraint UNIQUE_WHEN_NOT_NULL could be added,
but there are plenty of situations when you want UNIQUE to work like it does today.
Posted by Michael Lato on 9/24/2007 at 2:11 PM
The July CTP of SQL Server 2008 allows for filtered indexes with the UNIQUE keyword. You can now create a UNIQUE index "WHERE x is not NULL" to perform this operation. Of course, we still have to deal with it in other ways in 2000 and 2005.
Posted by Mark Yudkin on 9/24/2007 at 4:53 AM
Another possibility if backwards compatibility must be maintained is to add a "SET ANSI_UNIQUE=ON|OFF" option.
Posted by Steve Kass on 9/22/2007 at 7:51 PM
Another possibility is for the behavior of UNIQUE to depend on the compatibility level of the database. I think this would cause less to break than to tie the behavior to ANSI_NULLS. Only a couple of compatibility levels are typically supported, so the old nonstandard behavior would autodeprecate and disappear with the 90 compatibility level.
Sign in to post a workaround.
Posted by davidofmorris on 10/2/2007 at 10:31 AM
SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column
http://weblogs.sqlteam.com/mladenp/archive/2007/05/17/60209.aspx

UNIQUE Constraint vs NULL's
http://dimantdatabasesolutions.blogspot.com/2007/03/unique-constraint-vs-nulls.html

Posted by Dejan on 5/28/2008 at 8:46 AM
http://www.tsql.ca/Default.aspx?tabid=234&EntryID=55
Posted by Alan Singfield on 8/26/2008 at 3:55 AM
CREATE TABLE [dbo].[UNIQUENESS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [testing] [nvarchar](50) NULL,
    [testing_unique] AS (case when [testing] IS NULL then [id] end
),
CONSTRAINT [PK_UNIQUENESS] PRIMARY KEY CLUSTERED
(
    [id] ASC
)

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_UNIQUENESS] ON [dbo].[UNIQUENESS]
(
    [testing] ASC,
    [testing_unique] ASC
)


The computed column testing_uniqueness will copy the identity field if the value of testing is NULL. This means that the tuple (testing, testing_uniqueness) will be unique for all values where testing is null.

This will give the desired effect of as many nulls as you like, but no duplicate non-null values.




Posted by Fabrice MARGUERIE on 9/11/2011 at 9:22 AM
As posted by Microsoft in the comments:

The new filtered indexes feature supports this, e.g.:

create unique nonclustered index idx on dbo.DimCustomer(emailAddress)
where EmailAddress is not null;
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement