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@mitre.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
