Link to home
Start Free TrialLog in
Avatar of universaltruth
universaltruth

asked on

Require a unique compound key/index that excludes nulls or alternative solution

I allready have a database that contains a unique compound index for two columns in a Microsoft SQL Server 2005 Standard edition database.

These 2 columns are called WholesaleID and SupplierID. Combined they make up a unique key.

The same Supplier will never sell a product that has the same WholesaleID. I want to make sure I do not enter the same product twice if I can help it so I use the WholesaleID when it exists with the supplierID. However while there will be no Null SupplierIDs some of the WholesaleID values are unknown for some suppliers and will be Null.

This violates the Constraint as 2 Null values are considered the same value.

I require a unique compound constraint that prevents me from inserting values that are NOT Unique with the exception of Null values for the WholesaleID.

What is the most effective and efficient way of achieving this or should I be looking at a completely different method then having a Unique compount index that excludes nulls. Ive read that such an index is dodgy.
 
Microsoft SQL Server 2008 enables you to use an efficient solution ---  define a unique filtered index based on a predicate that excludes NULLs.

Im not sure if SQL Server 2005 edition supports this option or if there is a work around that is just as efficient.

A detailed solution would be much appreciated.
Avatar of k_murli_krishna
k_murli_krishna
Flag of India image

For INSERT/UPDATE/DELETE/SELECT nulls are same but internally each null in a column is different.
You can have a composite unique index or constraint/key with columns being nullable as well. But primary key columns cannot be NULL.

Hence, the constraint will not be violated for either unique key or index. Declaring a primary key automatically creates an index in the background but unique key/constraint may not. One thing is sure you can have like:
WholesaleID integer null,
SupplierID integer not null,
CONSTRAINT uk_wid_sid UNIQUE(WholesaleID, SupplierID)

The other way is to have both as NOT NULL and have either composite PK/UK/UI and handle from application i.e. in your INSERT/UPDATE/DELETE/SELECT statements using COALESCE(WholesaleID, 0) OR NULLIF(WholesaleID, 0) where in if NULL is encountered for WholesaleID in a particular row, then replace it with 0 or some other suitable value instead of it.

You can create a schema bound view with a unique index.

The schema bound view would be on wholesaleid and supplierid where wholesaleid is not null.

Then create a unique index on the view.
I MAY have misinterpreted the question.  My solution was if you want to allow multiple NULL values but not allow duplicating NOT NULL values.
Avatar of universaltruth
universaltruth

ASKER

I think everyone that has replied may have miss-understood my question.

I have a unique index which contain two colums WholesaleID and SupplierID. They make up a unique index ONLY WHEN COMBINED. Both these columns allow Nulls. However I have yet to find away to allow more then one Null for the same column if the other column contains a repeating value

So the following example produces the following error:

Cannot insert duplicate key row in object 'dbo.Products' with unique index 'IX_WholesaleID_SupplierID'.
The statement has been terminated

WholesaleID                  SupplierID
1                  1
2                  1
1                  2
2                  2
Null                  3
Null                  3 <<< This row violates the Unique index
WholesaleID                  SupplierID
1                          1
2                          1
1                          2
2                          2
Null                          3
Null                          3 <<< This row violates the Unique index
Now it is more clearly understood. If you want unique index on both columns and WholesaleID can be null, then why insert like this:
Null                          3
Null                          3 <<< This row violates the Unique index

I mean why do you want to insert a duplicate key value if you originally wanted all sets of pairs to be distinct from each other. Handling from application using COALESCE or NULLIF will also not help since 0 will be inserted/updated instead of Null and still constraint/index violation will occur.

You have to cure it from design angle i.e. you want 2 records with SupplierID= 3 not knowing what to put in WholesaleID. You just have SupplierID as NOT NULL and WholesaleID as NULL with no PK/UK/UI on both of them together. You may have only on WholesaleID as per your data.

You can have a separate PK or NOT NULL UNIQUE column called AgentID which uniquely identifies each record as per 1st normal form which always has a SupplierID though it maybe duplicate but an optional WholesaleID.

You can also try 2 different tables Wholesale and Supplier where in WholesaleID is foreign key in Supplier table referencing WholesaleID in wholesale table using a optional relationship. AgentID will still be PK of Supplier table with SupplierID as NOT NULL but allowing duplicate values.
I do not see how having an AgentID column would prevent a Unique compount Index (SupplierID-WholesaleID) with the exception of Null values

For example:

AgentID          SupplierID          WholesaleID
1                      1                        1
2                      1                        2
3                      2                        1
4                      2                        NULL
5                      2                        NULL  << This would violate the constraint

I want SupplierID and WholesaleID to make up a Unique Key when they are combined  1-1, 1-2, 2-1. If I am correct this is called a compound Index.

However I want to make an exception and allow Non-Unique Keys when the WholeSaleID is Null    2-Null, 2-Null

This would enable me to check if I have entered the same product (WholesaleID) for the values that are available. But it would take acount of 2 different suppleirs that have the same WholesaleID
"I think everyone that has replied may have miss-understood my question."

Actually I think I COMPLETELY understood your question in post http:#22972532

Run this small sample and you will see.
use tempdb
go
create table TheStuff
     (a   varchar(10) null
     ,b   varchar(10) null
     )
GO
create view vw_TheStuff
with schemabinding
as
select a,b from dbo.theStuff
where a is not null
and b is not null
GO
create unique clustered index uidx_vw_theStuff on vw_TheStuff(a,b)
GO
insert into TheStuff (a,b) values('test',null)
GO
insert into TheStuff (a,b) values('test',null)
GO
insert into TheStuff (a,b) values('test','ABC')
GO
insert into TheStuff (a,b) values('test','DEF')
GO
insert into TheStuff (a,b) values('test','DEF')
GO
select * from TheStuff
 
GO
 
drop view vw_TheStuff
go
drop table TheStuff

Open in new window

I get the following error when I run the above code:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.vw_TheStuff' with unique index 'uidx_vw_theStuff'.

It appears to be the same kind of error I get when I try and add a duplicate Null value and confirms my problem.
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
BrandonGalderisi is correct. Each NULL is treated differently internally, hence, you can have unique constraint/index on multiple columns even if 1 or more of them is NULL. It is only the values other than NULL that must be a unique set for the unique constraint/index. For primary key we need to keep all columns as NOT NULL.