UNIQUE constraint on nullable column

  • Is there a recommended way of dealing with the problem caused by a table that has a nullable column with a UNIQUE constraint only allowing a single NULL for that column?

    Thanks.

    Paul

     

     

  • The only thing you could do would be to remove the unique constraint.



    Shamless self promotion - read my blog http://sirsql.net

  • Ok thanks.  I thought that was probably the case, but I wondered whether there was a workaround.

    Paul

     

  • Yes...according to the BOL you can have a UNIQUE column as long as only one NULL exists.

    You can also combine two columns to make one unique constraint.

    -SQLBill

  • If you absolutely must implement your design, you could try using a trigger to enforce uniqueness for non-null values. See the basic example below. Note that in this example, if a multi-row INSERT statement contains even one duplicate then the whole INSERT is rolled back.

    DROP TABLE nulltest

    CREATE TABLE nulltest

    (

      id int NOT NULL PRIMARY KEY,

      col varchar(10) NULL

    )

    CREATE INDEX ix_nulltest_col ON nulltest (col)

    GO

    CREATE TRIGGER tiu_nulltest ON nulltest FOR INSERT, UPDATE

    AS

      IF (SELECT COUNT(*)

            FROM inserted i JOIN nulltest n ON i.col = n.col

           WHERE i.col IS NOT NULL

             AND i.id <> n.id

         ) > 0

      BEGIN

        RAISERROR('Duplicate data INSERTed or UPDATEd', 16, 1)

        ROLLBACK TRANSACTION

      END

    GO

    INSERT nulltest (id, col) VALUES (1, 'Row 1')

    INSERT nulltest (id, col) VALUES (2, 'Row 2')

    INSERT nulltest (id, col) VALUES (3, 'Row 3')

    INSERT nulltest (id, col) VALUES (4, null)

    INSERT nulltest (id, col) VALUES (5, null)

    INSERT nulltest (id, col) VALUES (6, 'Row 6')

    INSERT nulltest (id, col) VALUES (7, null)

    GO

    INSERT nulltest (id, col) VALUES (8, 'Row 6')   -- this should fail

    INSERT nulltest (id, col) VALUES (9, 'Row 6')   -- this should fail

    GO

    INSERT nulltest (id, col) VALUES (10, 'Row 10')

    GO

    SELECT * FROM nulltest ORDER BY id

  • Another possibility may be to create a view which only brings back non-null records and then set a unique index on the view.


    Tony

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

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