April 17, 2009 at 12:51 am
Dear friends,
Can unique constraint have more then one nulls?
If yes then how?
Some body told me it is possible..
Mithun
April 17, 2009 at 1:55 am
Can unique constraint have more then one nulls?
The answer is NO.Unique constraint can have only one NULL value.All the rows in that column should be unique and so it accepts only one NULL.
April 17, 2009 at 2:00 am
No. .. its not possible to have multiple null's for a column which is having unique constriant...
create table temp
(
id int unique
)
insert into temp
values (1)
insert into temp
values (null)
insert into temp
values (null)
drop table temp
Try this....
April 17, 2009 at 2:07 am
It is possible, one way i know which is pasted below, wanted to know anyone knows the other simple way????
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE t1 (id INT, title VARCHAR(20))
GO
CREATE VIEW v1
WITH SCHEMABINDING
AS
SELECT id, title
FROM dbo.t1
WHERE id IS NOT NULL
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX CI_V1_ID
ON dbo.v1 (id);
GO
INSERT INTO v1 (id, title)
SELECT 1, 'title 1' UNION ALL
SELECT 2, 'title 2' UNION ALL
SELECT 3, 'title 3' UNION ALL
SELECT 4, 'title 4' UNION ALL
SELECT null, 'title null'
-- this fails because it clashes with the clustered index on the view
INSERT INTO v1 (id, title)
SELECT 1, 'title 5'
-- this is ok because it doesn't clash.
INSERT INTO v1 (id, title)
SELECT NULL, 'title 5'
-- we see that the table can contain only unique values and more than one null
SELECT * FROM t1
SELECT * FROM v1
GO
DROP VIEW dbo.v1
GO
DROP TABLE dbo.t1
Thanks
Mithun
April 17, 2009 at 3:56 am
mithun.gite (4/17/2009)
It is possible, one way i know which is pasted below, wanted to know anyone knows the other simple way????
Not in 2005. In SQL 2008 there's an easy way with filtered indexes, but that's 2008 only.
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
April 17, 2009 at 6:20 am
Hi
In SQL2k5 it is possible by a detour over a non-unique index and a CHECK constraint:
CREATE TABLE TestCheck
(
Id INT NOT NULL IDENTITY,
UniqueText VARCHAR(30) NULL
)
GO
CREATE INDEX IX_TestCheck_UniqueText ON TestCheck (UniqueText)
GO
CREATE FUNCTION ufn_CheckTestUnique
(
@Id INT,
@txt VARCHAR(30)
)
RETURNS BIT
AS
BEGIN
DECLARE @Ret BIT
IF (@txt IS NULL)
SET @Ret = 1
ELSE IF EXISTS (SELECT TOP(1) 1 FROM TestCheck WHERE Id != @Id AND UniqueText = @txt)
SET @Ret = 0
ELSE
SET @Ret = 1
RETURN @Ret
END
GO
ALTER TABLE TestCheck
ADD CONSTRAINT CK_TestCheck_UniqueText
CHECK
(dbo.ufn_CheckTestUnique(Id, UniqueText) = 1)
GO
INSERT INTO TestCheck SELECT 'Hello'
INSERT INTO TestCheck SELECT NULL
INSERT INTO TestCheck SELECT NULL
INSERT INTO TestCheck SELECT 'World'
INSERT INTO TestCheck SELECT 'Hello'
GO
SELECT * FROM TestCheck
GO
/*
DROP TABLE TestCheck
DROP FUNCTION dbo.ufn_CheckTestUnique
*/
It's a workaround but it can handle the problem.
Greets
Flo
April 17, 2009 at 6:31 am
Florian Reischl (4/17/2009)
HiIn SQL2k5 it is possible by a detour over a non-unique index and a CHECK constraint:
CREATE TABLE TestCheck
(
Id INT NOT NULL IDENTITY,
UniqueText VARCHAR(30) NULL
)
GO
CREATE INDEX IX_TestCheck_UniqueText ON TestCheck (UniqueText)
GO
CREATE FUNCTION ufn_CheckTestUnique
(
@Id INT,
@txt VARCHAR(30)
)
RETURNS BIT
AS
BEGIN
DECLARE @Ret BIT
IF (@txt IS NULL)
SET @Ret = 1
ELSE IF EXISTS (SELECT TOP(1) 1 FROM TestCheck WHERE Id != @Id AND UniqueText = @txt)
SET @Ret = 0
ELSE
SET @Ret = 1
RETURN @Ret
END
GO
ALTER TABLE TestCheck
ADD CONSTRAINT CK_TestCheck_UniqueText
CHECK
(dbo.ufn_CheckTestUnique(Id, UniqueText) = 1)
GO
INSERT INTO TestCheck SELECT 'Hello'
INSERT INTO TestCheck SELECT NULL
INSERT INTO TestCheck SELECT NULL
INSERT INTO TestCheck SELECT 'World'
INSERT INTO TestCheck SELECT 'Hello'
GO
SELECT * FROM TestCheck
GO
/*
DROP TABLE TestCheck
DROP FUNCTION dbo.ufn_CheckTestUnique
*/
It's a workaround but it can handle the problem.
Greets
Flo
Thx Flo,
Great so using Check constraint we can have multiple nulls with unique values..
coollll
Thx again for ur reply
Mithun
April 17, 2009 at 2:15 pm
See this technique posted by Steve Kass
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply