x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

CREATE TYPE2 UNIQUE WHERE NULL INDEX

This pops up every so often, as it does Here.  The post title is the DB2 syntax for achieving a unique index that allows for nulls.  I always felt that since Nulls are not equal to anything, not even themselves, then how could you get a dup key violation.   To be fair, even in DB2 you can not have a primary key that allows more than 1 Null.  But you can create a unique index in lieu of the the PK that will allow nulls.  This allows the ability to build non-identifying relationships, something I believe is lacking in SQL Server. 

In any event, there are all kinds of work arounds to do this, as David shows here.  Also, with my exposure to SQL Server, it is the general concensus to avoid nulls at all cost.  I was raised differently.  After reading Bonnie Bakers article, “Much Ado about Nulls” I am convinved more now than ever (well, ok, that was about 5 years ago).

Anyway, here's a method using a trigger where you can simulate this.

Update:  I forgot to add code to handle the update...duh.  The additional code is added in Purple for anyone who has already downloaded the code.  Or you can just cut and paste it all over.

Note:  With the new code added for the update, it's kind of a hack, since I'm using the identity column.  What I should really do, instead of using the surrogate, is to perform a logical update to the natural keys(Col1 and Col2) by performing a DELETE and a new INSERT operation.  This eliminates the dependancy on the surrogate.  But as an example, it serves it's purpose here.  Thanks for reading.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE
myTable99 (
 [id] int IDENTITY (1, 1) NOT NULL ,
 Col1 varchar (15) NULL ,
 Col2 varchar (14) NULL ,
)


CREATE INDEX myTable99_Col1 ON myTable99(Col1)
CREATE INDEX myTable99_Col2 ON myTable99(Col2)
GO

CREATE TRIGGER myTrigger99 ON myTable99
INSTEAD OF INSERT, UPDATE
AS
  BEGIN

 SET NOCOUNT ON
 IF
EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
          ON t.Col1 = i.Col1
         AND t.Col2 = i.Col2
       WHERE i.Col1 IS NOT NULL
         AND i.Col2 IS NOT NULL)
 OR EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
          ON t.Col1 = i.Col1
       WHERE i.Col1 IS NOT NULL
         AND i.Col2 IS NULL AND t.Col2 IS NULL)
 OR EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
   ON t.Col2 = i.Col2
       WHERE i.Col1 IS NULL AND t.Col1 IS NULL
         AND i.Col2 IS NOT NULL)
   BEGIN
  RAISERROR
('Violation of Logical PRIMARY KEY constraint. Cannot insert duplicate key in object myTest99', 16, 1)
     ROLLBACK TRANSACTION
   END
 ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
  INSERT INTO myTable99(Col1, Col2) SELECT Col1, Col2 FROM inserted
 ELSE
  UPDATE t
     SET Col1=i.Col1
       , Col2=i.Col2
    FROM myTable99 t INNER JOIN inserted i
      ON t.[id] = i.[id]


 SET NOCOUNT OFF
  END

GO


INSERT INTO myTable99(Col1, Col2)
SELECT '1' , Null UNION ALL
SELECT '1' , '1'  UNION ALL
SELECT Null, '1'  UNION ALL
SELECT '2' , '2'  UNION ALL
SELECT '2' , Null UNION ALL
SELECT Null, '2'  UNION ALL
SELECT Null, '3'  UNION ALL
SELECT '3' , Null UNION ALL
SELECT '3' , '3'
GO

SELECT * FROM myTable99
GO

UPDATE myTable99 SET Col1 = 4 WHERE id = 9

UPDATE myTable99 SET Col2 = 4 WHERE id = 9

SELECT * FROM myTable99
GO

--Dup Keys

INSERT INTO myTable99(Col1, Col2)
SELECT '1', Null
GO

INSERT INTO myTable99(Col1, Col2)
SELECT '1', '1'
GO

INSERT INTO myTable99(Col1, Col2)
SELECT Null, '1'
GO

UPDATE myTable99 SET Col2 = 4 WHERE id = 9
GO

SET NOCOUNT OFF
DROP TRIGGER
myTrigger99
DROP TABLE myTable99
GO

Print | posted on Wednesday, April 20, 2005 1:23 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: CREATE TYPE2 UNIQUE WHERE NULL INDEX

I think a slightly more elegant way to handle this is via an indexed view. Create the following indexed view against the table in order to enforce it:

CREATE VIEW un_myTable99
WITH SCHEMABINDING
AS
SELECT Col1, Col2
FROM dbo.myTable99
WHERE Col1 IS NOT NULL
OR Col2 IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX IX_un_myTable99 ON un_myTable99(Col1, Col2)
GO

4/21/2005 4:48 PM | Adam Machanic
Gravatar

# re: CREATE TYPE2 UNIQUE WHERE NULL INDEX

I love your approach, but there's a much simpler way to write the trigger that overcomes some of your shortcomings here:
1) You've got to repeat your basic test three times, which is already a lot, but the number would increase geometrically if you wanted more columns in the logical key.
2) The trigger (especially the UPDATE part) will break if columns are added or dropped from the table

This seems simpler to me:
CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE
AS
IF EXISTS (
SELECT 1
FROM myTable99 a INNER JOIN inserted i
ON a.Col1 = i.Col1 AND a.Col2 = i.Col2 -- nulls will drop out; they don't equal anything
GROUP BY a.Col1, a.Col2
HAVING COUNT(*) > 1 -- if there is more than one row with given values, uniqueness has been violated
)
BEGIN
RAISERROR ('Violation of Logical PRIMARY KEY constraint. Cannot insert duplicate key in object myTest99', 16, 1)
ROLLBACK TRANSACTION
END
GO
8/23/2005 7:50 PM | rnickel
Gravatar

# re: CREATE TYPE2 UNIQUE WHERE NULL INDEX

That was really handy rnickel - thanks!
9/28/2005 5:03 AM | Trevor
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET