I'm having problems with MySQL constraints

Question

Hi guys.

I'm having some trouble using constraints correctly.

I have three tables, 'item', 'store' and 'link_item_store'. An Item can be in one or many stores, and one or many stores can have an item. Since this is a many to many relationship, I'm using 'link_item_store' to normalize.

If I delete an item, I have to remove all instancs of that item in the 'link_item_store' table. And the same goes for store. This is my table and it's constraints:

CREATE TABLE `link_item_store` (
  `fk_storeID` int(11) unsigned NOT NULL,
  `fk_itemID` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`fk_storeID`,`fk_itemID`),
  KEY `fk_storeID` (`fk_storeID`),
  KEY `fk_itemID` (`fk_itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

ALTER TABLE `link_item_store`
  ADD CONSTRAINT `link_item_store_ibfk_2` FOREIGN KEY (`fk_itemID`) REFERENCES `link_item_store (`fk_itemID`) ON DELETE CASCADE,
  ADD CONSTRAINT `link_item_store_ibfk_1` FOREIGN KEY (`fk_storeID`) REFERENCES `link_item_store` (`fk_storeID`) ON DELETE CASCADE;

And heres is an example list:

fk_storeID, fk_itemID, itemName, storeName
11  7277 item 1 Test store
11  7278 item 2 Test store
11  7280 item 3 Test store
12  7277 item 1 Test store 2
12  7278 item 2 Test store 2
12  7290 item 4 Test store 2
35  7295 item 4 Test store 4
35  7299 item 5 Test store 4
35  7300 item 6 Test store 4
35  7302 item 7 Test store 4

My problem is that if I delete 'item 7' from 'item' table, ALL references are deleted from link_item_store. :(

What to do?

Update: By having these two constraints on the table, I'm not able to insert new data to the table. I get the following error message:

Unable to query local database <b>Cannot add or update a child row: a foreign 
key constraint fails ( `link_item_store`, CONSTRAINT 
`link_item_store_item_ibfk_1` FOREIGN KEY (`fk_storeID`) REFERENCES 
`link_item_store` (`fk_storeID`) ON DELETE CASCADE)</b><br> INSERT INTO 
link_item_store (fk_storeID, fk_itemID) VALUES ('11', '7295')

Update 2 - Solution: Michael pointed out to me that the ADD CONSTRAINT didn't look right. On a closer look, I saw that he was right. This is the code that is generated by PhpMuAdmin upon adding constraints:

ALTER TABLE `link_item_store` ADD FOREIGN KEY ( `fk_itemID` ) REFERENCES `mydatabase`.`link_item_store` (`fk_itemID`) ON DELETE CASCADE ;

As you can see, the foreign key is reffering to itself! This defenetily have to be a bug in PhPMyAdmin.

The solution was to change reference. This works and I no longer have problems adding new records to the table:

ALTER TABLE `link_item_store` ADD FOREIGN KEY ( `fk_itemID` ) REFERENCES `mydatabase`.`item` (`id`) ON DELETE CASCADE ;

Answer 1

If you delete item 7 how can other stores have it?

The behavior you are seeing is referred to as "cascading deletes". Every row that references a row you delete with an FK is also deleted. There is also the normal behavior of not allowing the delete, and a "set null" behavior that will change all FKs to the deleted item to NULL. It sounds like that's what you want.

Here is a link talking about the different options.

Answer 2

The syntax of your foreign keys seems unusual to me.

Instead of:

ADD CONSTRAINT `link_item_store_ibfk_2` FOREIGN KEY (`fk_itemID`) REFERENCES `link_item_store` (`fk_itemID`) ON DELETE CASCADE

Try:

 ADD CONSTRAINT `link_item_store_ibfk_2` FOREIGN KEY (`fk_itemID`) REFERENCES item (`itemID`) ON DELETE CASCADE

(Assuming itemID is the name of the correct column on the item table)