Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Unique constraint on a column (design decision)

    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?

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  3. #3
    Join Date
    Dec 2004
    Posts
    22
    Ahh, but that does sound like it would solve the problem quite well! Thanks for the suggestion.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by Garoad View Post
    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);

  6. #6
    Join Date
    Dec 2004
    Posts
    22
    I thought everyone drank the Oracle kool aid?
    Thanks for the replies.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •