By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

unique key with optional field

P: n/a
Is it possible to have the field as a unique key and a optional one?
It is like.. for example, office code has to be unique (cannot be
duplicated with the same code) and it could be null too.

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Here are three possible methods (in each case, Colx is the nullable, unique
column):

1. Use an indexed view:

CREATE VIEW SomeTable_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT colx
FROM dbo.SomeTable
WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL (colx)

2. Use an index with a calculated column:

CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL, foo
AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))

3. Use a trigger:

CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
ON SomeTable FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN SomeTable AS S
ON I.colx = S.colx AND I.keycol<>S.keycol)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate values not permitted',16,1)
END

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.