2

I would like to add a unicity constraint on my MySQL table. This table contains four columns : 

ID | NAME | ADDRESS1 | ADDRESS2

This constraint must check that for a new row, the new address1 and address2 are not contained either in ADDRESS1 nor ADDRESS2.

Example : 

INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('A', 'B'); -- OK
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('C', 'D'); -- OK
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('E', 'A'); -- Fails because A exists in ADDRESS1
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('D', 'F'); -- Fails because D exists in ADDRESS2

Is there a way to define a such constraint ?

dounyy
  • 666
  • 12
  • 24
  • your design is bad for addresses (use separate table for them) anyway you can use unique indices – mychalvlcek Jan 03 '14 at 10:34
  • I know the design could be way better. But unfortunately I cannot change it, that is why I am looking for a such constraint. – dounyy Jan 03 '14 at 10:40
  • You could always create a trigger. I don't believe MySQL has CHECK constraints, which would be ideal here. –  Jan 03 '14 at 10:42
  • Yes actually the CHECK constraints exist but are ignored. – dounyy Jan 03 '14 at 10:45
  • @dounyy Haha - CHECK exists, but does nothing? –  Jan 03 '14 at 10:47
  • @Nicarus The manual says it is ignored... http://stackoverflow.com/questions/7522026/how-do-i-add-a-check-constratint-to-a-table?answertab=active#tab-top – dounyy Jan 03 '14 at 10:49
  • @dounyy What version of MySQL are you using? – peterm Jan 03 '14 at 11:05
  • @dounyy Can you please show exact table schema e.g. as a result of `SHOW CREATE TABLE `. I'm particularly interested in `NOT NULL` constraints on any of your columns to give you a solution with a trigger. – peterm Jan 03 '14 at 11:17
  • @peterm `NAME` and `ADDRESS1` are unique and not null. `ADDRESS2` is unique but can be null. – dounyy Jan 03 '14 at 11:32

1 Answers1

1

You can do it with a BEFORE trigger this way

CREATE TRIGGER tg_bi_mytable
BEFORE INSERT ON mytable
FOR EACH ROW
  SET NEW.address1 = IF(EXISTS
     (
       SELECT * 
        FROM mytable 
       WHERE address1 IN(NEW.address1, NEW.address2) 
          OR address2 IN(NEW.address1, NEW.address2)
     ), NULL, NEW.address1);

Note: Since you're using a MySQL version that lacks SIGNAL the trick is to violate NOT NULL constraint on one of the columns when rows with the same address have been found.

Here is SQLFiddle demo. Uncomment one of the last insert statements and click Build Schema. These inserts won't succeed.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Well, that is a solution that fit my needs, the only little thing is that violating the `NOT NULL` constraint returns an error message which is not very appropriate. I am going to look for a way to define a custom one. – dounyy Jan 03 '14 at 12:52
  • I finally decided to manage this unicity inside my Java code. That is easier for me, and it is, to my point of view, better than using a workaround/hack to replace a feature that is not supported by my version of MySQL. However, I accept your answer, since it could have helped me, and will probably help some other people. Thank you a lot. – dounyy Jan 03 '14 at 15:08