Unique constraint on a column (design decision)

Garoad · #1 (**permalink**) 01-28-10, 17:06

Here's the situation:
We have a table containing a column that should have unique values in it at all times. BUT there's one exception: when a flag is set in another column (say a "deleted" flag), those "unique" values can be re-used within the non-"deleted" subset of items.

So the values in the "unique" column aren't unique if you count re-used values that were used in previous (deleted) rows.

(The 'deleted' rows cannot be physically deleted and must be retained for security and auditing purposes, so that's not the answer.)

For data integrity purposes, is it worth putting a unique constraint on this column by moving the "deleted" items to an archive table? (Keep in mind a few tables reference items in this primary table, so those references would need to be updated.)

From a db design perspective would it be better to simply leave the table as is and "trust" application and data migration logic to enforce this 'unique' constraint on "non-deleted" rows? Is this bad practice or reasonable given the requirement to retain deleted rows?

MarkATrombley · #2 (**permalink**) 01-28-10, 17:17

You could enforce the constraint with an insert trigger (before allowing insert make sure no undeleted rows match). That is my best suggestion, but perhaps others have something else.

Garoad · #3 (**permalink**) 01-28-10, 17:25

Ahh, but that does sound like it would solve the problem quite well! Thanks for the suggestion.

Pat Phelan · #4 (**permalink**) 01-28-10, 17:26

I would treat the current "deleted" column as a "generation" column. Generation zero would be the current or active generation. When you wish to "retire" a row, set the generation to 1 + Max(generation) for the current ID column, then add a new generation zero row for the ID value.

You can then declare a unqiue constraint on the combination of the ID column and the generation column. No two rows can have the same pair of values, so only one row with a given ID value can have a zero generation to make it current.

The generation values have to be unique, but that's fast and easy to manage. This lets the database guarantee (by the unique constraint) that code has to behave.

-PatP

shammat · #5 (**permalink**) 01-28-10, 17:46

Quote:

Originally Posted by Garoad

Here's the situation:
We have a table containing a column that should have unique values in it at all times. BUT there's one exception: when a flag is set in another column (say a "deleted" flag), those "unique" values can be re-used within the non-"deleted" subset of items.

So the values in the "unique" column aren't unique if you count re-used values that were used in previous (deleted) rows.

You posted in the general database section, but this solution is highly DBMS dependent. It's called a partial index.

With Postgres you can do the following:

Code:

CREATE UNIQUE INDEX idx_partial_unique 
  ON your_table (unique_column)
   WHERE flag <> 'deleted';

I think (but I'm not sure) that SQL Server 2008 has a similar syntax as Postgres.

With Oracle you could do something like this:

Code:

CREATE UNIQUE INDEX idx_partial_unique 
  ON your_table ( CASE 
          WHEN flag = 'deleted' THEN NULL 
          ELSE unique_column
     END);

Garoad · #6 (**permalink**) 01-28-10, 17:58

I thought everyone drank the Oracle kool aid?

Thanks for the replies.

Internet Services

Web Development

Digital Marketing

Consumer Tech