Column with UNIQUE values but multiple NULL values

Question

Post reply

Column with UNIQUE values but multiple NULL values

sql server developer

SSCarpal Tunnel

Points: 4746
More actions
May 28, 2012 at 4:37 pm

#392364

Hi All,
I've a column in which the values should be unique but can have multiple NULL values
Unique constraint will accept only 1 NULL value but in my situation it should be able to allow multiple NULL values.
I appreciate if some could give me some suggestions to accomodate this.
Thanks in advance!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Just checking... are you actually on sql 2005?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 2

sql server developer (5/28/2012)
Unique constraint will accept only 1 NULL value but in my situation it should be able to allow multiple NULL values. I appreciate if some could give me some suggestions to accomodate this.

In SQL Server 2008 and later, use a filtered index. On SQL Server 2005, a related solution is to use an indexed view:

CREATE TABLE dbo.Example

(

col1 integer NULL,

);

GO

CREATE VIEW dbo.ExampleUnique

WITH SCHEMABINDING AS

SELECT e.col1

FROM dbo.Example AS e

WHERE e.col1 IS NOT NULL;

GO

CREATE UNIQUE CLUSTERED INDEX cuq

ON dbo.ExampleUnique (col1);

GO

INSERT dbo.Example VALUES (1);

INSERT dbo.Example VALUES (2);

INSERT dbo.Example VALUES (NULL);

-- Error

INSERT dbo.Example VALUES (2);

SELECT * FROM dbo.Example AS e

GO

DROP VIEW dbo.ExampleUnique;

DROP TABLE dbo.Example;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 3

Thanks so much. I'm on SQL Server 2008 and decided to use Filtered Index on that column. However the issue i'm having is that i would like to create a foreign key reference to this col from a different table and it gives an error saying it cannot be reference. Is there any other way to create a foreign key reference to this column that can allow multiple NULL values and unique non-null values?

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 4

sql server developer

SSCarpal Tunnel

Points: 4746

May 29, 2012 at 12:48 pm

#1494333

I'm on SQL Server 2008 R2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sql server developer (5/29/2012)
Thanks so much. I'm on SQL Server 2008 and decided to use Filtered Index on that column. However the issue i'm having is that i would like to create a foreign key reference to this col from a different table and it gives an error saying it cannot be reference. Is there any other way to create a foreign key reference to this column that can allow multiple NULL values and unique non-null values?

Just curious, what is the SQL you are issuing, and what is the exact error message you are getting?

sql server developer SSCarpal Tunnel Points: 4746 More actions · Answer 6

CREATE TABLE dbo.TestAsset(AssetID INT IDENTITY(1,1) PRIMARY KEY, SerialNo VARCHAR(100))

--CREATE Filtered UNIQUE Index to allow multiple records of NULL and Unique non-null values in serialNo column

CREATE UNIQUE NONCLUSTERED INDEX UK_TestAsset_SerialNo

ON TestAsset (SerialNo)

WHERE SerialNo IS NOT NULL;

--Populate TEST data

INSERT INTO TestAsset VALUES('1001')

INSERT INTO TestAsset VALUES('1002')

INSERT INTO TestAsset VALUES('1003')

INSERT INTO TestAsset VALUES(NULL)

--==Secondary table that should reference Serial Number in the above table

CREATE TABLE dbo.TestLease(SerialNo VARCHAR(100), LeaseNo VARCHAR(100))

--==foreign key relationship

ALTER TABLE TestLease

ADD CONSTRAINT fk_TestLease_TestAsset FOREIGN KEY(SerialNo)REFERENCES TestAsset(SerialNo)

GO

--==Error: While creating foreign key relationship using above query

--Msg 1776, Level 16, State 0, Line 1

--There are no primary or candidate keys in the referenced table 'TestAsset' that match the referencing column list in the foreign key 'fk_TestLease_TestAsset'.

--Msg 1750, Level 16, State 0, Line 1

--Could not create constraint. See previous errors.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

sql server developer (5/29/2012)
CREATE TABLE dbo.TestAsset(AssetID INT IDENTITY(1,1) PRIMARY KEY, SerialNo VARCHAR(100))
--CREATE Filtered UNIQUE Index to allow multiple records of NULL and Unique non-null values in serialNo column
CREATE UNIQUE NONCLUSTERED INDEX UK_TestAsset_SerialNo
ON TestAsset (SerialNo)
WHERE SerialNo IS NOT NULL;
--Populate TEST data
INSERT INTO TestAsset VALUES('1001')
INSERT INTO TestAsset VALUES('1002')
INSERT INTO TestAsset VALUES('1003')
INSERT INTO TestAsset VALUES(NULL)
INSERT INTO TestAsset VALUES(NULL)
--==Secondary table that should reference Serial Number in the above table
CREATE TABLE dbo.TestLease(SerialNo VARCHAR(100), LeaseNo VARCHAR(100))
--==foreign key relationship
ALTER TABLE TestLease
ADD CONSTRAINT fk_TestLease_TestAsset FOREIGN KEY(SerialNo)REFERENCES TestAsset(SerialNo)
GO
--==Error: While creating foreign key relationship using above query
--Msg 1776, Level 16, State 0, Line 1
--There are no primary or candidate keys in the referenced table 'TestAsset' that match the referencing column list in the foreign key 'fk_TestLease_TestAsset'.
--Msg 1750, Level 16, State 0, Line 1
--Could not create constraint. See previous errors.

You are trying to create a foreign key relationship on a column that is neither the primary key nor unique in the original table. By definition this will not work

From the MSDN page.

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

http://msdn.microsoft.com/en-us/library/ms175464%28v=sql.105%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul White SSC Guru Points: 150467 More actions · Answer 8

Sean Lange (5/29/2012)
You are trying to create a foreign key relationship on a column that is neither the primary key nor unique in the original table. By definition this will not work
From the MSDN page.
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
http://msdn.microsoft.com/en-us/library/ms175464%28v=sql.105%29.aspx

BOL has the following in CREATE TABLE (Transact-SQL):

FOREIGN KEY REFERENCES
Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column or columns in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table[/color]. Foreign keys on computed columns must also be marked PERSISTED.

Nevertheless, this is a quirk of SQL Server and not ANSI SQL. The entry does not mention that the UNIQUE INDEX cannot be a filtered index, but that makes sense if you consider that a FOREIGN KEY must be enforced for the whole range of values, and a filtered index cannot provide that guarantee. The RI solution to the requirement presented here would be an assertion, but SQL Server does not support those yet.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

sql server developer (5/29/2012)
Thanks so much. I'm on SQL Server 2008 and decided to use Filtered Index on that column. However the issue i'm having is that i would like to create a foreign key reference to this col from a different table and it gives an error saying it cannot be reference. Is there any other way to create a foreign key reference to this column that can allow multiple NULL values and unique non-null values?

No, there is no way to do this today using DRI. You could use triggers to enforce the relationship, or rework the design.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi