Thread: Deleted Flag/Unique Constraint

Is it possible to declare a unique constraint in combination with a deleted flag?

For example, if I have a table like this:

CREATE TABLE
(
  ID NOT NULL PRIMARY KEY,
  Key VARCHAR(32) NOT NULL,
  Value VARCHAR(32) NOT NULL,
  Deleted INT NOT NULL DEFAULT 0
);

can I declare a unique constraint that only checks Key and Value when Deleted = 0?

Thanks,
Bryan

On Mar 29, 2007, at 17:39, Bryan Murphy wrote:

> Is it possible to declare a unique constraint in combination with a
> deleted flag?
> For example, if I have a table like this:
>
> CREATE TABLE
> (
>   ID NOT NULL PRIMARY KEY,
>   Key VARCHAR(32) NOT NULL,
>   Value VARCHAR(32) NOT NULL,
>   Deleted INT NOT NULL DEFAULT 0
> );
>
> can I declare a unique constraint that only checks Key and Value
> when Deleted = 0?

I gather you want the constraint enforced only if the item is not
deleted - duplicate <key, value> pairs are fine if all but one are
deleted, yes?  You can't do this with this table definition as is,
but ...

Unique constraints only apply to groups of column values that are non-
null, so, if you'd be willing to do something like the following, I
think you can get roughly what you want:

CREATE TABLE keyValues  -- Dunno what name you wanted
(
   ID serial PRIMARY KEY,  -- Dunno what type you wanted
   Key VARCHAR(32) NOT NULL,
   Value VARCHAR(32) NOT NULL,
   notDeleted Boolean DEFAULT True,
   UNIQUE (Key, Value, notDeleted),
   CHECK (notDeleted = True)
);

I've changed your flag to be a boolean - this is not strictly
necessary, but conveys its semantics better, I think.  More
importantly, I've reversed its polarity, and arranged for it to only
have values of True or NULL.  When it is NULL, which we can interpret
as meaning it is not notDeleted, i.e., it is deleted, the unique
constraint will not apply.

This is an abuse of NULL, and means that your app will have to set
the flag to NULL, rather than False, but I believe it satisfies your
requirements.  You could hide the abuse behind a view, if necessary.

- John Burger
   MITRE

Bryan Murphy wrote:

> Is it possible to declare a unique constraint in combination with a
> deleted flag?
>
> For example, if I have a table like this:
>
> CREATE TABLE
> (
>   ID NOT NULL PRIMARY KEY,
>   Key VARCHAR(32) NOT NULL,
>   Value VARCHAR(32) NOT NULL,
>   Deleted INT NOT NULL DEFAULT 0
> );
>
> can I declare a unique constraint that only checks Key and Value when
> Deleted = 0?

Yes, something like this:

CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE
deleted=0;

-Jonathan

Thanks!  That works great!

Bryan

I think the other guys suggestion will work better. ;)

Really, the table was just an example off the top of my head.  I believe we do use a boolean as the deleted flag.  We primarily use it to track the user who originally created an item (even if their account is deleted).  It's a bit like a financial system where you can't retroactively change the data. We always want to know who was associated with the original transaction, even long after their account was deleted.
Thanks for the suggestion though!

Bryan

Bryan Murphy wrote:

> I think the other guys suggestion will work better. ;)

Good lord, yes.  Dunno what I was thinking - I use partial indexes
all the time, and I know a unique constraint is implemented with an
index.  Just got carried away, I guess. :)

- John Burger
   MITRE

Bryan Murphy wrote:
> I think the other guys suggestion will work better. ;)
>
> Really, the table was just an example off the top of my head.  I believe
> we do use a boolean as the deleted flag.  We primarily use it to track
> the user who originally created an item (even if their account is
> deleted).  It's a bit like a financial system where you can't
> retroactively change the data. We always want to know who was associated
> with the original transaction, even long after their account was deleted.
> Thanks for the suggestion though!

(Your post would have been clearer if it were inline with the material quoted,
rather than top-posted.)

Your requirements analysis raises an important point, one that I've seen
misused in practice.  In your data model, "deleted" is a business concept -
the example "deleted account" is an account that is NOT deleted in the
database, but exists with a business attribute "deleted".

I've seen that sort of flag used absent any business requirement to maintain
current information about a "deleted" fact, but instead as meta-data to audit
the database usage. The bad effect was that all business logic had to account
for the "deleted" flag even though it had no semantic in the business domain.

I speculate that a separate meta-data table is more apt for such a use,
although I continue to investigate scenarios where it makes sense to keep
historic facts in the same table with current facts. Naturally this opens up
the world of temporal databases.

My hypothesis is that the business-domain semantics of the facts that a table
models must be temporal in order to keep historic facts therein, If the
purpose for the history is not rooted in the business domain, then such facts
must be in separate tables from those that model the business domain. So a
"deleted" account attribute merits a column in the "accounts" table, but
record deletion facts just to monitor database usage should be separate. Both
might make sense in the same implementation, yielding "accounts" and
"accountsaudit" tables.

I am not yet convinced that I have the answers on this matter.

-- Lew

*Jonathan Hedstrom* wrote
> Yes, something like this:
>
> CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE deleted=0;

My only concern here is
> Note:  The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes
toenforce unique constraints could be considered an implementation detail that should not be accessed directly.  
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>

Why scare us off?.  The semantics of unique indexes vary from DBMS to DBMS.

For example, for IBM DB2:
> When UNIQUE is used, null values are treated as any other values. For example, if the key is a single column that may
containnull values, that column may contain no more than one null value. 
<ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_US/db2s2e90.pdf>

But in PostgreSQL:
> When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are
notconsidered equal. 
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>

MySQL evinces both behaviors, depending on which storage engine a table uses.
> An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not
applyto NULL  values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values
forcolumns that can contain NULL.  
<http://dev.mysql.com/doc/refman/5.0/en/create-index.html>

So I guess if portability of the full semantics is an issue, be careful.

-- Lew

Yeah, personally, I'm not entire 100% convinced of the concept myself and am actively investigating alternatives.  Most of what I'm doing right now is simple research, as well as trying out a few ideas to see what works in practice and what doesn't.  Unfortunately, this is one of those areas where I have yet to find a lot of guidance on the issue.

Bryan