Sql Can I Have A "conditionally Unique" Constraint On A Table?

I've had this come up a couple times in my career, and none of my local peers seems to be able to answer it. Say I have a table that has a 'Description' field which is a candidate

Solution 1:

If you are using SQL Server 2008 a Index filter would maybe your solution:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

This is how I enforce a Unique Index with multiple NULL values

CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL

Solution 2:

In the case of descriptions which are not yet completed, I wouldn't have those in the same table as the finalized descriptions. The final table would then have a unique index or primary key on the description.

In the case of the active/inactive, again I might have separate tables as you did with an "archive" or "history" table, but another possible way to do it in MS SQL Server at least is through the use of an indexed view:

CREATETABLE Test_Conditionally_Unique
(
    my_id   INTNOTNULL,
    active  BIT NOTNULLDEFAULT0
)
GO
CREATEVIEW dbo.Test_Conditionally_Unique_View
WITH SCHEMABINDING
ASSELECT
        my_id
    FROM
        dbo.Test_Conditionally_Unique
    WHERE
        active =1
GO
CREATEUNIQUE CLUSTERED INDEX IDX1 ON Test_Conditionally_Unique_View (my_id)
GO

INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 1)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 0)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1)
INSERTINTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1)    -- This insert will fail

You could use this same method for the NULL/Valued descriptions as well.

Solution 3:

Thanks for the comments, the initial version of this answer was wrong.

Here's a trick using a computed column that effectively allows a nullable unique constraint in SQL Server:

createtable NullAndUnique 
    (
    id intidentity, 
    name varchar(50),
    uniqueName ascasewhen name isnullthencast(id asvarchar(51)) 
        else name +'_'end,
    unique(uniqueName)
    )

insertinto NullAndUnique defaultvaluesinsertinto NullAndUnique defaultvalues-- Worksinsertinto NullAndUnique defaultvalues-- not accidentally :)insertinto NullAndUnique (name) values ('Joel')
insertinto NullAndUnique (name) values ('Joel') -- Boom!

It basically uses the id when the name is null. The + '_' is to avoid cases where name might be numeric, like 1, which could collide with the id.

Solution 4:

I'm not entirely aware of your intended use or your tables, but you could try using a one to one relationship. Split out this "sometimes" unique column into a new table, create the UNIQUE index on that column in the new table and FK back to the original table using the original tables PK. Only have a row in this new table when the "unique" data is supposed to exist.

OLD tables:

TableA
ID    pk
Col1  sometimes unique
Col...

NEW tables:

TableA
ID
Col...

TableB
ID   PK, FK to TableA.ID
Col1 unique index

Solution 5:

Oracle does. A fully null key is not indexed by a Btree in index in Oracle, and Oracle uses Btree indexes to enforce unique constraints.

Assuming one wished to version ID_COLUMN based on the ACTIVE_FLAG being set to 1:

CREATEUNIQUE INDEX idx_versioning_id ON mytable 
  (CASE active_flag WHEN0THENNULLELSE active_flag END,
   CASE active_flag WHEN0THENNULLELSE id_column   END);