NoppNLearner NoppNLearner - 17 days ago 10
MySQL Question

How to prevent duplicate pair to be inserted into lookup table?

I'm trying to make relationship between

CAR
and
ITEM
tables.

Since a car can have the same component as other cars, and an item can be used in many cars. I decide to use many-to-many relationship for this issue.

This issue force me to remove
PK
from both
car_idcar
and
item_list_iditem_list
on the middle table.

But...How I can prevent entry of duplicate pair(
red box below
)from being inserted


Additional info as @Barmar requested:

CREATE TABLE `car_item` (
`id_car_item` int(11) NOT NULL AUTO_INCREMENT,
`car_idcar` int(11) NOT NULL,
`item_list_iditem_list` int(11) NOT NULL,
PRIMARY KEY (`id_car_item`),
UNIQUE KEY `car_idcar_UNIQUE` (`car_idcar`),
UNIQUE KEY `item_list_iditem_list_UNIQUE` (`item_list_iditem_list`),
KEY `fk_car_has_item_list_item_list1_idx` (`item_list_iditem_list`),
KEY `fk_car_has_item_list_car1_idx` (`car_idcar`),
CONSTRAINT `fk_car_has_item_list_car1` FOREIGN KEY (`car_idcar`) REFERENCES `car` (`idcar`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_car_has_item_list_item_list1` FOREIGN KEY (`item_list_iditem_list`) REFERENCES `item_list` (`iditem_list`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


Full schema creating script here: http://pastebin.com/1vPYxswd

enter image description here

Answer

You should not have unique keys on each column, you should have a unique key on the combination of columns. This allows each column to be duplicated by itself, but not the pair of them. So you can have multiple references to the same car, and multiple references to the same item_list, but not multiple links between the same car and item_list.

CREATE TABLE `car_item` (
   `id_car_item` int(11) NOT NULL AUTO_INCREMENT,
   `car_idcar` int(11) NOT NULL,
   `item_list_iditem_list` int(11) NOT NULL,
   PRIMARY KEY (`id_car_item`),
   UNIQUE KEY `car_idcar_iditem_list_UNIQUE` (`car_idcar`, `item_list_iditem_list`),
   CONSTRAINT `fk_car_has_item_list_car1` FOREIGN KEY (`car_idcar`) REFERENCES `car` (`idcar`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_car_has_item_list_item_list1` FOREIGN KEY (`item_list_iditem_list`) REFERENCES `item_list` (`iditem_list`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

See Creating UNIQUE constraint on multiple columns in MySQL Workbench EER diagram