Re: Deleted Flag/Unique Constraint

["John D. Burger" <john(at)mitre(dot)org>]

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