Re: Deleted Flag/Unique Constraint

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