Unique key on whole mysql table?

Question

Suppose I have a mysql table with two columns: A and B. Is it possible to have a unique key so that I can only insert a value only once in either A or B (once in the whole table)?

So if column A contains 'qwe' and B contains 'asd' then these two values cannot be inserted anymore in either column.

this will not work:

UNIQUE KEY `A` (`A`,`B`),
UNIQUE KEY `A_2` (`A`),
UNIQUE KEY `B` (`B`),
UNIQUE KEY `B_2` (`B`,`A`)

thanks.

edit: I was able to accomplish this with the following trigger:

delimiter |
create trigger unique_check before insert on mytable
       for each row begin
              declare alreadyexists integer;
          select count(*) > 0 into alreadyexists from mytable
                 where A=NEW.B or B=NEW.A;
          IF alreadyexists = 1 THEN begin
             DECLARE dummy INT;
         SELECT 'A OR B already exists' INTO dummy FROM mytable
            WHERE nonexistent = 'value';
 end;
 END IF;
 END;|

However, I do not see the 'A OR B already exists' error message, but:

ERROR 1054 (42S22): Unknown column 'nonexistent' in 'where clause'

Thanks again!

Answer 1

Yes it's possible.

1 way is

You need to create a BEFORE INSERT TRIGGER and return error if the value is already found in other columns/tables.

From this blog post

MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger? On EfNet’s #mysql someone asked:

How do I make a trigger abort the operation if my business rule fails?

In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and deliver a meaningful error message. The MySQL Stored Procedure FAQ says this about error handling:

SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.

Perhaps MySQL 5.2 will include SIGNAL statement which will make this hack stolen straight from MySQL Stored Procedure Programming obsolete. What is the hack? You’re going to force MySQL to attempt to use a column that does not exist. Ugly? Yes. Does it work? Sure.

CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

Another way

You can also do with Transactions

use a procedure with transaction to insert data into transactional table (InnoDB),

In the trigger write on error condition:

set @error=1; 

In the procedure something like this:

set @error=0; 
start transaction 
do insert 
if @error>0 then rollback; 
else commit; 

Answer 2

The proper (and simple) way to do this relationally is to create two tables, T1 and T2, where T2 has a foreign key relationship (many-to-one) back to T1. The unique index/constraint is declared on T2.yourUniqueColumn, and if you must differentiate between the values in that column, add another column to T2:

   T1
   id
   foo


   T2
   t2id
   t1id  fk references T1
   yourUniqueColumn  [unique index/constraint]
   extraColumnToDescribeTheValueInUniqueColumn     

You could initially populate T2 in this way (assumes auto-incrementing PK in T2):

  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select t1.id as t1id, T1.A as yourUniqueColumn, 'A' as extraColumn from T1


  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select T1.id as t1id, T1.B as yourUniqueColumn, 'B' as extraColumn from T1

As a rule-of-thumb, whenever you find yourself doing things procedurally when working with a relational database, it's time to take a step back and consider refactoring.

Answer 3

You could use COALESCE to get the first null value from A or B providing both do not get set http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce