Null values with unique key constraint

Question

Null values with unique key constraint

peacesells

SSCrazy

Points: 2882
More actions
February 5, 2012 at 8:08 pm

#391479

how can we insert multiple null values in a column with unique key constraint? I tried with created a view partitioned view but i cudn't even until there...
the error reads:
Msg 4512, Level 16, State 3, Procedure myView, Line 4
Cannot schema bind view 'myView' because name 'Employee1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
although my database is not remote.
thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

I could be wrong but I'm pretty sure it can't be done. Unique columns allow only one NULL. You might be able to pull something off with a filtered index but I've not tried it myself.

The error on the view is a different story. You have to do just what the error message say... use 2 part naming on table names and column names. I recommend using table aliases on the column names rather than full table names.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The usual trick is a unique filtered index.

CREATE UNIQUE INDEX <index name> ON <table name>(<column name>)

WHERE <Column name> IS NOT NULL

Uniqueness only enforced over the non-null portion of the table.

Has to be a unique index because unique constraints can't be filtered.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

peacesells SSCrazy Points: 2882 More actions · Answer 3

GilaMonster (2/6/2012)
The usual trick is a unique filtered index.
CREATE UNIQUE INDEX <index name> ON <table name>(<column name>)
WHERE <Column name> IS NOT NULL
Uniqueness only enforced over the non-null portion of the table.
Has to be a unique index because unique constraints can't be filtered.

Sweet. Thank You much!!!

mtassin SSC-Insane Points: 23099 More actions · Answer 4

GilaMonster (2/6/2012)
The usual trick is a unique filtered index.
CREATE UNIQUE INDEX <index name> ON <table name>(<column name>)
WHERE <Column name> IS NOT NULL
Uniqueness only enforced over the non-null portion of the table.
Has to be a unique index because unique constraints can't be filtered.

Blah Gail!

I finally got a change to show Jeff I knew something he didn't... and you beat me to it by 12 hours... :/

I've fallen in love with filtered indexes.... epecially filtered unique indexes... so many uses.... so little time.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]