Avoiding the same identifier used in many two-to-many relationships
foo_bars is a table with many two columns with two columns pointing to foo.id I want foo_bars. [Id1, id] for unique key How to avoid the same identifier used in the foo_bars entry.
i.e. insert into foo_bars (2,2) - How to 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)
+3
source to share
2 answers
You can create a trigger like this:
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 ;
0
source to share