我遇到MySQL限制问题

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.

我有三个表,'item','store'和'link_item_store'。物品可以在一个或多个商店中,并且一个或多个商店可以具有物品。由于这是一个多对多的关系,我使用'link_item_store'来规范化。

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:

如果我删除了一个项目,我必须删除“link_item_store”表中该项目的所有实例。商店也一样。这是我的表,它的约束:

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. :(

我的问题是,如果我从'item'表中删除'item 7',则会从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:

更新2 - 解决方案:迈克尔向我指出ADD CONSTRAINT看起来并不合适。仔细看,我看到他是对的。这是PhpMuAdmin在添加约束时生成的代码:

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.

正如你所看到的,外键正在为自己提供帮助!这肯定是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 ;

2 个解决方案

#1

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)

(假设itemID是项表上正确列的名称)

#2

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

如果删除第7项,其他商店如何拥有它?

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.

您看到的行为称为“级联删除”。引用您使用FK删除的行的每一行也将被删除。还有一个不允许删除的正常行为,以及一个“set null”行为,它将所有FK更改为已删除的项目为NULL。听起来这就是你想要的。

Here is a link talking about the different options.

这是一个谈论不同选项的链接。

#1