how to exclude nulls from unique index

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to exclude nulls from unique index

Author

Topic

AlanS
Starting Member

28 Posts

Posted - 2004-10-26 : 12:56:40

I have a non-key field which is null in most records, and I want to ensure that if a non-null value is entered in that field, it does not duplicate any other non-null value in that field. An ordinary unique index won't work, as it will only allow one record with a null value. I've already tried:

use MyDatabase
create unique index MyField_Unique_Index on MyTable(MyField)
with ignore null

which produces this error:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'null'.

Is there any way to do this via an index or constraint? Better yet would be a way to specify it in Visio, which is where we do all our database design and generate the script that creates the database.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-26 : 14:44:07

In many cases, the best solution is to break this table apart into two tables, with a 1-1 relationship.

i.e., if you have:

TableX: ID, f1, f2, f3, f4

and f4 must have unique values if it is not null, a good solution is often to break it out of TableX by removing that column and creating a new table:

TableY: ID, f4

Then, you establish a 1-1 relationship from TableY to TableX on the ID column. To enforce your constriant, you can then create a regular unique index on f4 in TableY. If the value of f4 is null, then you simply do not have an entry in TableY.

combine them back together in a SELECT or in a VIEW:

select X.ID, X.f1, X.f2, X.f3, Y.F4
from TableX X
left outer join TableY Y
on X.ID = Y.ID

- Jeff

AlanS
Starting Member

28 Posts

Posted - 2004-10-26 : 14:49:23

Thanks for the suggestion, Jeff. At this stage of the project, I don't think it will be practical to break this table (which is central to the overall design) into two. Most likely, we'll simply have to add code to the one screen where this field may be edited, to check that any non-null value is unique.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 14:52:48

Check this out:

http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-26 : 14:58:30

A third approach could be to use a trigger to protect the data.

Edit:
Triggers can be modeled in Visio I believe.
Jeffs suggestion is good db design.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 22:19:13

You could create a VIEW

SELECT MyColumn
FROM MyTable
WHERE MyColumn IS NOT NULL

and then put a unique index on the view's column.

However, I believe that this index won't be used to optimise queries unless you use SQL Enterprise Edition. And in my experience it creates other difficulties - the view becomes bound to the table so you ahve to drop the view to alter the table and so on.

Based on my experiences with the couple of indexed views we've got I would use a trigger to prevent insertion of data that attempted to create a duplicate.

The computed column solution and the need to "mess with ARITHABORT" is a PITA in my experience. We frequently have to work around the fact that client connections somehow struggle to get ARITHABORT to the correct setting - so I'd vote against that one too!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources