Re: Deleted Flag/Unique Constraint

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

On 3/29/07, John D. Burger <john(at)mitre(dot)org> wrote:
>
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>