Re: Deleted Flag/Unique Constraint

Jonathan Hedstrom <jhedstrom@xxxxxxxx> · Thu, 29 Mar 2007 17:05:12 -0700

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