3

foo_bars is a many-2-many table with both columns pointing to foo.id I want foo_bars.[id1, id] to for a unique key How do I avoid same id being used in a foo_bars entry.

i.e. insert into foo_bars (2,2) - How do I avoid this?

mysql> create table foo (id int(11), name varchar(255));
Query OK, 0 rows affected (0.49 sec)

mysql> desc foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table foo_bars (id1 int(11), id int(11));
Query OK, 0 rows affected (0.34 sec)

mysql> desc foo_bars;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Rpj
  • 5,348
  • 16
  • 62
  • 122

2 Answers2

1

you can add a unique contatraint

CHECK (id1<>id)

create table foo_bars (id1 int(11), id int(11),CHECK (id1<>id));
albert hou
  • 66
  • 5
  • No it doesn't work. I don't want it to work if they are pointing to the same id. In the following case, its 2. I want the first insert to fail. mysql> insert into foo_bars values (2,2); Query OK, 1 row affected (0.04 sec) mysql> insert into foo_bars values (2,2); ERROR 1062 (23000): Duplicate entry '2-2' for key 'ID_CONSTRAINT' mysql> insert into foo_bars values (2,3); Query OK, 1 row affected (0.08 sec) – Rpj Jul 21 '15 at 08:46
  • I don't understand your comment. What is the work you want? – albert hou Jul 21 '15 at 08:50
  • As I said, I want the first insert to fail - i.e. they are pointing to the same row on both columns. – Rpj Jul 21 '15 at 08:50
  • CHECK (id1 <>id) replace the constraint with this – albert hou Jul 21 '15 at 08:54
  • 1
    [The check constraint doesn't work in MySQL](http://stackoverflow.com/a/2115641/1492578). – John Bupit Jul 21 '15 at 08:58
  • Yes it doesn't. mysql> drop table foo_bars; Query OK, 0 rows affected (0.11 sec) mysql> create table foo_bars (id1 int(11), id int(11),CHECK (id1<>id)); Query OK, 0 rows affected (0.29 sec) mysql> insert into foo_bars values (2,2); Query OK, 1 row affected (0.04 sec) mysql> insert into foo_bars values (2,2); Query OK, 1 row affected (0.09 sec) mysql> insert into foo_bars values (2,2); Query OK, 1 row affected (0.04 sec) – Rpj Jul 21 '15 at 08:59
0

You can create a trigger, as follows:

DELIMITER $$

CREATE TRIGGER `test_id_uniqueness` BEFORE INSERT ON `foo_bars`
FOR EACH ROW
BEGIN
    IF NEW.id = NEW.id1 THEN
        SIGNAL SQLSTATE '12345';
        SET MESSAGE_TEXT := 'foo_bars.ID and foo_bars.ID1 cannot be the same';
    END IF;
END$$

DELIMETER ;
John Bupit
  • 10,406
  • 8
  • 39
  • 75