Unique constraint on MS SQL?!
I have a strong feeling that MS SQL knows I don’t like it. I don’t say it’s a bad database, but sometimes it’s really “interesting”. 
OK, so what I faced this time? Let’s start with basic facts. The NULL value in database is a special value. NULL and NULL is again NULL, NULL and False is again NULL and NULL isn’t equal to NULL. Two days ago I was creating a chain of records in database (which isn’t too relationalish, but …) and I needed unique constraint on one column. No problem you may think. So did I. But not in MS SQL.
Check this example:
1> create table test(id int primary key, foo int); 2> create unique index idx_text on test(foo); 3> go 1> insert into test values (1, null); 2> go (1 rows affected) 1> insert into test values (2, null); 2> go Msg 2601, Level 14, State 1, Server X2-001SQLEXPRESS, Line 1 Cannot insert duplicate key row in object 'dbo.test' with unique index 'idx_text '. The statement has been terminated.
What a mess! Looks like somebody in MS SQL engine team thinks NULL == NULL. After some railing, testing on Firebird and asking my friend about Oracle, I googled: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299229. Looks like I’m not alone; feeling better.
Luckily I’m working on MS SQL 2008 and I can use filtered indices. Created unique index with where clause where <column> is not null and I’m done. But boy that was a bitter finding. Hope I’ll not be punched to the face with these basics again in an at least two months.
There's 12 Comments So Far
September 26th, 2009 at 22:37
Jestli se nepletu tak se takto SQL chova minimalne od verze 7. A myslim si, ze ANSI SQL to tak i definuje.
September 26th, 2009 at 23:13
No nemam primo specifikaci pred sebou. Ale vzhledem k tomu, jak je definovana logika na NULL hodnotach a ze se tak chova Firebird, Oracle i jsem to tak nejak ocekaval
a je to i reportovano, tak bych si porad dovolil tvrdit, ze unique constraint by mel povolovat vice NULL hodnot, ale pouze jednu not-NULL. Lec muzu se mylit.
September 30th, 2009 at 13:18
Start with:
“”"
set ansi_nulls on
go
“”"
October 1st, 2009 at 21:08
Even with set ansi_nulls on; it doesn’t work for me. Same error.
Looking into BOL, it doesn’t say anything about the unique and nulls, only the nulls comparison in queries etc. (although it’s related).
April 5th, 2011 at 06:41
“Luckily I’m working on MS SQL 2008 and I can use filtered indices” – Indeed, it’s quite very useful in moments like these.
@cincura.net “Even with set ansi_nulls on; it doesn’t work for me. Same error.” – lol. i though i was the only one who thought about it. i also tried it and i get the same error..
What should i do?
April 5th, 2011 at 07:28
“Looks like I’m not alone; feeling better.
”
That’s right!, your not alone, many of us have a problem with this error.
Is there any way to solve this?
April 5th, 2011 at 10:24
@cincura.net Yeah I get the same error when I tried to set ansi_nulls on. I assume there must be something with oracle connection but I was wrong, it was all about MS SQL. Anyway, maybe we can try to update Microsoft office to its latest version for some other features not seen on 2008. I think 2011 is already out, not sure really but MS SQL 2010 is much better.
April 5th, 2011 at 14:09
I am using MS SQL in my software development project and hey it’s really good and work better with me. But there some instances where it bugs something in your code.
@cincura, Evangeline, and Cherish: Try to visit to MS SQL Support or reply to my comment for I could give a fix of it. What exactly you’re doing in your codex?
April 14th, 2011 at 04:56
MS SQL is good. but yeah, you’re right some error occured sometimes. however its better.
Who Linked To This Post?
Share your thoughts, leave a comment!