Unique Constraint w nulls, compare columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique Constraint w nulls, compare columns

Author

Topic

rhinton21
Starting Member

10 Posts

Posted - 2008-06-20 : 16:32:11

Hi Guys.

I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?

For Example:

This is what I want

ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2

This is what I DO NOT want:

ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null

I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:52:36

[code]DECLARE @Sample TABLE (Col1 INT, Col2 INT)

INSERT @Sample
SELECT 1, 2 UNION ALL
SELECT 1, NULL UNION ALL
SELECT 1, 1 UNION ALL
SELECT NULL, NULL

SELECT Col1,
Col2,
CASE
WHEN Col1 = Col2 THEN 0
WHEN Col1 IS NULL AND Col2 IS NULL THEN 0
ELSE 1
END
FROM @Sample

SELECT Col1,
Col2
FROM @Sample
WHERE NOT (Col1 = Col2 OR Col1 IS NULL AND Col2 IS NULL)[/code]This is the constraint you want.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources