SQL unique contrast if the column is set to x

Question

Is there a way in SQL to create a constraint that a column must be unique if a particular column has a specific value?

Example: rows are not actually deleted, but marked as “deleted” in the database. And inside the “not deleted” strings, ValueA must 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 was thinking of something like a CHECK constraint, however I don't know how to do this.

Answer 1

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

Answer 2

Can you change the design a bit?

It seems to me that you have a list of "stuff." For each ValueA, there is one active “thingy” at any given time. This is best modeled as follows:

• Remove ValueA and removed from your main Thingies table.

• Create a new ActiveThingies table with ValueA and ID columns. Protect this table by making ValueA a unique or primary key. (You may also need to make the identifier unique, depending on whether one identifier can represent more than 1 ValueA).

Now, use the ActiveThingies table to manage the current record at any time. To change the active (not deleted) entry for "foo", update the ID column in ActiveThingies.

To get a list of items that have not been deleted, join the two tables.

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

Answer 3

MySQL ignores CHECK restrictions, so you cannot do this in MySQL, as you could in another database.

Here's a hack. Unique A + value restriction removed. When deleting rows you cannot use only 1, they must be 1, 2, 3 ...

This at least allows you to do this on the server side in MySQL, but introduces a step. When marking a line for deletion, you must first find max (deleted), add 1, and include this value when marking for deletion.

Answer 4

Divide the table into two tables: one that has a UNIQUE constraint for ValueA, and the other does not. Use view + triggers to join two tables. Sort of:

 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 syntax of CREATE TRIGGER, but you know what I mean.)

Answer 5

There is a workaround - create another deleted_on column

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

and make a unique key for both ValueA and deleted_on

UNIQUE KEY not_deleted (ValueA, deleted_on)

When gently deleting insert insertion NOW() for deleted_on value