[jens]

Hi!

I would really like to use a constraint on a column that enforces unique entries while still allowing any number of entries with null in that column. I.e. if ((the inserted value is unique) or (the inserted value is null)) then the insertion is accepted else the insertion is not accepted.

Is that possible? How?

Thanks
Jens

[thava]

yes it is possible

but not straight forward
first you have to create a view for that column with schema binded

then create a unique index for that column
and that's all

/****** Object:  Table [dbo].[test]    Script Date: 02/08/2011 14:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[No] [int] NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[test]  WITH CHECK ADD  CONSTRAINT [FK_test_test] FOREIGN KEY([Id])
REFERENCES [dbo].[test] ([Id])
GO
ALTER TABLE [dbo].[test] CHECK CONSTRAINT [FK_test_test]
Go

CREATE VIEW  vw_test WITH SCHEMABINDING
AS 
SELECT  t.No FROM dbo.test t WHERE no IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX idxuno ON vw_test(no)
GO

INSERT INTO test  (No)VALUES( 1 )
GO
SELECT * FROM test 
GO
INSERT INTO test  (No)VALUES( NULL )
GO
INSERT INTO test  (No)VALUES( NULL )
GO
SELECT * FROM test 
GO
INSERT INTO test  (No)VALUES( 1 )
--- not this line will generate a error
GO

just run this query in your master database

happy codng

This post has been edited by thava: 08 February 2011 - 02:38 AM

[jens]

Hi, thanks for the reply!

I found another solution which does not require a view here.

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

Regards
Jens

[baavgai]

Thats clever; thanks.

Note, the NULL in MS SQL is a major failing. It makes me twitch every time I have to deal with it.

Codd ( father of relational database theory ) defines a null value as:

Quote

Rule 3: Systematic treatment of null values:
The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
-- http://en.wikipedia.org/wiki/Codd's_12_rules

Oracle, the first implemented version of SQL, interpreted this rule as "A null can be assigned but it cannot be equated with anything: even itself." As a result, in an Oracle database ( and several others ), you can have a unique constraint and as many nulls as you like. Null isn't equal to null; it isn't equal to anything.

It's a challenging concept for many. Including, apparently, Microsoft.

Edit: Almost forgot about this! I think I had a problem with it once, but for completeness. MS does offer a way to be ANSI compliant. SET ANSI_NULLS

This post has been edited by baavgai: 08 February 2011 - 05:15 AM

[jens]

baavgai, on 08 February 2011 - 01:10 PM, said:

Edit: Almost forgot about this! I think I had a problem with it once, but for completeness. MS does offer a way to be ANSI compliant. SET ANSI_NULLS

Thank you. That was interesting. After a quick read of the page I'm a little confused however. I do understand what you quoted on the NULL != NULL and as a consequence one will not be able to "SELECT .... WHERE xxx <> NULL" but how does one get only rows that have xxx not null or rows that have xxx set to null in that case?

Regards
Jens

[baavgai]

You just said it. "Where Field!=NULL" is not the same as "Where Field NOT NULL". That's part of the reason for the syntax. It's a boolean statement, not an equality.

[thava]

jens, on 08 February 2011 - 02:51 AM, said:

Hi, thanks for the reply!

I found another solution which does not require a view here.

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

Regards
Jens

a computed column and a unique constraint that's great,
now what happen when there is a combination of primary keys
i think this logic will not work

[baavgai]

Interesting observation. Regardless of PK, you could do it:

CREATE TABLE #dupNulls (
	FirstName nvarchar(30) not null,
	LastName nvarchar(60) not null,
	IdNum int,
	nullbuster as (case when IdNum is null then FirstName+LastName else '~' end),
	CONSTRAINT dupNulls_uqIdNum UNIQUE (IdNum,nullbuster),
	CONSTRAINT PkdupNulls primary key (FirstName,LastName)
)

However, if you have a compound PK then you aren't using identity, so we can still use it:

CREATE TABLE #dupNulls (
	FirstName nvarchar(30) not null,
	LastName nvarchar(60) not null,
	IdNum int,
	NotNullKey int identity,
	nullbuster as (case when IdNum is null then NotNullKey else 0 end),
	CONSTRAINT dupNulls_uqIdNum UNIQUE (IdNum,nullbuster),
	CONSTRAINT PkdupNulls primary key (FirstName,LastName)
)

[thava]

fine and enough