SQL Server 2000/2005 – Creating Unique Constraints allowing multiple NULLs

pts.
Tags:
Desktops
Management
Microsoft Windows
OS
Security
Servers
SQL Server
T-SQL
Hi there!

I'm having a problem maintaining data integrity on a database table in MS SQL-Server 2000. To ensure maintaining the integrity I need to implement a Unique Constraint on one of my coloumns. The problem however is, that this coloumn also has to allow multiple NULL-values - but for every value in the coloumn not being NULL, the values has to be unique.

I have tried with the following code, and it almost works:
ALTER TABLE dbo.Table1 WITH NOCHECK ADD CONSTRAINT
	CK_Table1 CHECK ( 
		Col1 is null or 
		Col1 not in (Table1.Col1) 
	) 


The idea was to implement a check constraint, that for every data-change on the column would check to see, if the Col1 value is either being NULL or a non-existing value in the very same column.

However the last part ( Col1 not in (Table1.Col1) ) does not seem to work, and also I have a doubt, that the constraint would accept data-changes on another coloumn, since Col1 would then be compared to its own value - that being a value already in the table, the constraint might also fail due to that.

Does anyone have an idea how to come about this? Alternative data model design suggestions are of course also welcome!

Thanks! Jacob

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could try using a select statement for your in:
col1 not in (select table1.col1 from table1)

if that still doesn’t work, use before insert and before update triggers to do that same type of checking. You have the trigger make the insert or update fail if the check fails.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • LeChuck
    Hi Randym Thanks for your answer. I'd already tried the first solution, you suggested. When implementing CHECK constraints, you're not allowed to use select statements, so that solution was unfortunately not an option. The trigger way seems to be the only way to actually implement this kind of constraints. It has come to my attention though, that DB2 does support this kind of constraints. So the absence of the multiple NULL constraint in MS SQL-Server does not seem to be due to an actual bad-design-reason. However I've decided to go for a hole different kind of solution. The best way to design this solution is actually to use a third many-many relation table, using a Unique index for the Reference Key column, which ultimately results in the solution, I need, plus I avoid using unnessesary triggers, I get better optimized indexes, and the datamodel gets more accessible for others to read and understand! But the trigger solution would in some other cases for the same needs be the solution to go for! By the way... Be careful by using the syntax X not in (Select Y from Table). It usually leads to a Table Scan, making your query extremely slow for large tables. Instead use i.e. this syntax: select * from tab1 where not exists ( select null from tab2 where tab1.X = tab2.Y ) This way you're actually performing a join, selecting only the rows, not being part of the join-statement. Using a join, the SQL-Server is able to use your indexes instead of a table scan. Jacob
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following