I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Ever wanted to have have a table that contains unique values but needs to have multiple null values also?

Here's how to do it:

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

-- optional instead of trigger to disable the insert directly into the table
CREATE TRIGGER trg_t1_DisableInsert ON t1
INSTEAD OF INSERT
AS
BEGIN
    -- use 18 to stop further processing
    RAISERROR (40000, 18, 1, 'Use view dbo.v1 to insert data into table')
END
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

 

I believe that the code is self explanatory.

This came up in a presentation on the local MS Event called NT Conference (the link is in Slovene).

 

 

 

kick it on DotNetKicks.com

Print | posted on Thursday, May 17, 2007 12:27 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Why the trigger? IMHO directing data modifications through the view would only cause confusion since the view references only a subset of data. Imagine inserting a row where id is null - it will never show up in the view. If you're concerned with making sure the view is up-to-date, rest assured that the database engine will take care of that.

Anyway, there is an alternative to this method - it uses a computed column. Can you imagine how that would work? ;)

ML
5/17/2007 2:11 PM | ML
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

the trigger is just to illustrate the point how to limit input.
of course it's not needed at all.

I'd rather not imagine :))

will you post your presentation, Matija?


5/17/2007 2:17 PM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

I agree with you, Adam. The indexed view is much more elegant as it is less intrusive than the computed column.
However, it's always nice to at least be aware of alternatives. After all, SQL is not religion. :)

ML

p.s. Mladen, my presentation will be available soon.
5/18/2007 12:30 PM | ML
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Gee, that would make Celko one of the apostles... :)
5/18/2007 6:37 PM | ML
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

well with Celko's remarkable similarity to Anton LaVey, that would mean
that SQL is the Language of the damned :)))
5/18/2007 6:42 PM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Careful there, you should know by now that I look like Celko. In the nicest possible way. :)
5/18/2007 7:22 PM | ML
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

hmm.... i wouldn't be telling people that if i were you :)))
5/18/2007 7:34 PM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Looks can deceive. I hope. :)
5/18/2007 7:46 PM | ML
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

you hope correctly :))
5/18/2007 7:48 PM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

This looks very similar to an forum answer I posted on this link.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79160

I mentioned but did not explain in that post that there are issues with Indexed Views that make them not entirely painless (Painful enough make me not use them).

Before using them as a solution, you should read the following topics in SQL Server Books Online about Indexed Views.

SQL Server Architecture (SQL Server 2000) View Indexes
http://msdn2.microsoft.com/en-us/library/aa214336(SQL.80).aspx

SET Options That Affect Results
http://msdn2.microsoft.com/en-us/library/aa933126(SQL.80).aspx
"All connections using indexes on computed columns or indexed views must have the same settings for these seven options:

These six SET options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF."


5/22/2007 4:17 AM | Michael Valentine Jones
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

i haven't seen that topic michael.
thanx for the link.

i did set all of the options to on and 1 to off.
or am i missing something?
5/22/2007 11:14 AM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

I think your post is correct for the SET options.

The thing that I wanted to point out is that BOL seems to say that all connections that use the indexed view must use these same settings. That seems like a very hard thing to ensure, especially if SQL developers are not familiar with this issue. That is why I have always stayed away from them.

If the only reason for the indexed view is to enforce the constraint of unique except when null, maybe that will work OK. I would want to do some testing to make sure there were no issues.


5/22/2007 6:05 PM | Michael Valentine Jones
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

aaahhh... very good point. thanx!
5/22/2007 6:08 PM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
7/11/2007 7:30 AM | salman
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Using Trigger here doesn't allow records to be inserted into the table through the view. However without having the trigger and using just the view for manipulating records in the table serves the purpose.
7/19/2007 1:43 PM | Darshana
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

The problem is not about the identity column, but about the column id_bb.
This is a reference (not a Foreign Key though!) to the tblBb database.

When this reference is null, the workflow started by the record in this table is failing.
is there anyway to prevent column id_bb from having null value, the table schema is not Null identity column.
this uses trigger for update,insert and delelete

2/1/2008 11:43 AM | Tutu
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column


Just came across this bit of the discussion thread...

"well with Celko's remarkable similarity to Anton LaVey, that would mean
that SQL is the Language of the damned :))) "

Thought you'd like to know, Joe would get quite a kick out of this. (I know this for a fact, because he's my girlfriend's uncle). The resemblance is widely recognized. Apparently, in India, they call him the "SQL Devil".
1/9/2010 2:31 AM | Michael Foltz
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

Well michael, you're welcome to tell him about it :)
1/9/2010 2:41 AM | Mladen
Gravatar

# re: SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

You can insert into the underlying table directly no need to insert using the view... that what the SCHEMA_BINDING is for no?
1/27/2010 12:48 AM | MH
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET