> 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;