August 27, 2009 at 6:14 am
I have a column in a table which is declared as unique key.i have to pass null values more than one times.How can i pass null values?
Please guide me
August 27, 2009 at 6:26 am
You cant im afraid.
If its unique its unique
http://msdn.microsoft.com/en-us/library/ms175132(SQL.90).aspx
Maybe the best thing would be to split this column out to another table and reference it back to this table.
August 27, 2009 at 6:41 am
There's a couple of tricks to doing this in SQL 2005 and below, but there's no easy way of doing it.
One option is to create an indexed view that filters out the null values, then put the constraint on that. Got all the downsides of indexed views, plus it's a separate object in the database.
Second option is to create a persisted computed column and index that. Something like this.
Create Table UniqueNotNull (
id int identity primary key,
UniqueOrNullColumn varchar(10),
ToBeIndexed AS (Case WHEN UniqueOrNullColumn IS NOT NULL THEN UniqueOrNullColumn ELSE CAST(id AS varchar(10)) END) PERSISTED
)
GO
CREATE UNIQUE INDEX idx_UniqueOrNull ON UniqueNotNull (ToBeIndexed)
GO
insert into UniqueNotNull (UniqueOrNullColumn)
values ('abc')
insert into UniqueNotNull (UniqueOrNullColumn)
values (null)
insert into UniqueNotNull (UniqueOrNullColumn)
values ('def')
insert into UniqueNotNull (UniqueOrNullColumn)
values (null)
insert into UniqueNotNull (UniqueOrNullColumn)
values ('abc') -- fails
If there's any possibility of using SQL 2008 instead, this is trivially easy in SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2009 at 12:21 am
Thanx for solve my problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply