Re: Deleted Flag/Unique Constraint

[Jonathan Hedstrom <jhedstrom(at)desc(dot)org>]

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