SQL unique contraint if a column is set to x

is there a way in SQL to create the constraint that a column has to be unique, if a specific column has a specific value?

Example: the rows are not rea开发者_Go百科lly deleted, but marked as 'deleted' in the database. And within the 'not-deleted' rows, ValueA has to be unique:

ID    ValueA          ValueB            Deleted
-----------------------------------------------------
1     'foo'           10               0
2     'bar'           20               0
3     'bar'           30               1
4     'bar'           40               1
5     'foo'           50               0 --NOT ALLOWED

I thought of something like a CHECK constraint, however I don't know how to do this.

with SQL92 this is not possible, may be you could implement something with a trigger

Can you change the design a little bit?

It seems to me that you have a list of "thingies". For each ValueA, there's a single active "thingy" at any one time. This can best be modeled as follows:

1. Remove ValueA and Deleted from your main Thingies table.

2. Create a new table ActiveThingies with columns ValueA and ID. Protect this table by making ValueA a unique or primary key. (You may also need to make ID unique as well depending on whether a single ID can represent more than 1 ValueA).

Now, use the ActiveThingies table to control which record is current at any time. To change the active (non-deleted) record for "foo", update it's ID column in ActiveThingies.

To get your list of non-deleted items join the two tables.

With this design, however, you will lose the ability to remember the ValueA for "deleted" "thingies". If you need to remember those values, you will also need to include the ValueA column in Thingies.

There is workaround this problem - create another column deleted_on

deleted_on timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

and make unique key on both ValueA and deleted_on

UNIQUE KEY not_deleted (ValueA, deleted_on)

When soft deleting a record insert NOW() for value of deleted_on

MySQL ignores CHECK constraints, so you cannot do this in MySQL as you might in another database.

Here is a hack. Unique constraint on valueA + deleted. When deleting rows you cannot use just 1, they must be 1, 2, 3...

This at least lets you do it server-side in MySQL, but introduces a step. When marking a row for deletion, you have to first go find the max(deleted), add 1, and plug that value in when marking for deletion.

Split your table into two tables: One which has a UNIQUE constraint on ValueA and one that doesn't. Use a view+triggers to combine the two tables. Something like:

CREATE TABLE _Active (
    ID       INTEGER,
    ValueA   VARCHAR(255) UNIQUE,
    ValueB   INTEGER
);

CREATE TABLE _Deleted (
    ID       INTEGER,
    ValueA   VARCHAR(255), /* NOT unique! */
    ValueB   INTEGER
);

CREATE VIEW Thingies AS
    SELECT ID, ValueA, ValueB, 0 AS Deleted FROM _Active
UNION ALL
    SELECT ID, ValueA, ValueB, 1 AS Deleted FROM _Deleted;

CREATE TRIGGER _trg_ii_Thingies_Active
    INSTEAD OF INSERT ON Thingies
    FOR EACH ROW WHEN NOT NEW.Deleted
BEGIN
    INSERT INTO _Active(ID, ValueA, ValueB)
        VALUES (NEW.ID, NEW.ValueA, NEW.ValueB);
END;

CREATE TRIGGER _trg_ii_Thingies_Deleted
    INSTEAD OF INSERT ON Thingies
    FOR EACH ROW WHEN NEW.Deleted
BEGIN
    INSERT INTO _Deleted(ID, ValueA, ValueB)
        VALUES (NEW.ID, NEW.ValueA, NEW.ValueB);
END;

/* Add triggers for DELETE and UPDATE as appropriate */

(I'm not sure about the CREATE TRIGGER syntax, but you know what I mean.)