SQL can I have a "conditionally unique" constraint on a table?

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

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:

CREATE TABLE Test_Conditionally_Unique(    my_id   INT NOT NULL,    active  BIT NOT NULL DEFAULT 0)GOCREATE VIEW dbo.Test_Conditionally_Unique_ViewWITH SCHEMABINDINGAS    SELECT        my_id    FROM        dbo.Test_Conditionally_Unique    WHERE        active = 1GOCREATE UNIQUE CLUSTERED INDEX IDX1 ON Test_Conditionally_Unique_View (my_id)GOINSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (1, 0)INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (1, 0)INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (1, 0)INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (1, 1)INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (2, 0)INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)VALUES (2, 1)INSERT INTO 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.

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:

create table NullAndUnique     (    id int identity,     name varchar(50),    uniqueName as case         when name is null then cast(id as varchar(51))         else name + '_' end,    unique(uniqueName)    )insert into NullAndUnique default valuesinsert into NullAndUnique default values -- Worksinsert into NullAndUnique default values -- not accidentally :)insert into NullAndUnique (name) values ('Joel')insert into 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.