Re: Deleted Flag/Unique Constraint

["Bryan Murphy" <bryan(dot)murphy(at)gmail(dot)com>]

Thanks!  That works great!

Bryan

On 3/29/07, Jonathan Hedstrom <jhedstrom(at)desc(dot)org> wrote:

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