MYSQL 建模关系：设计和 UPDATE/DELETE
我试图完全理解关系，并且已经阅读了很多教程。我仍然在一些事情上徘徊（使用 MySQLWORKBENCH）： 
1. 每个用户都可以上传一个产品 2. 每个产品可以有多个类别、出价、评级等 3. 每个用户可以有多个电话号码 （还有更多，但这是基本设置）
这是正确的吗？： 1 - 我使用了 1:n 关系，因为每个用户都可以上传多个产品。 2 和 3。我使用了 n:m 关系，因为可能有多个产品具有多个类别、出价、评级等。
删除/更新： 我在有外键的任何地方都使用了 ON UPDATE CASCADE 和 ON DELETE CASCADE ...即“product”、“category_tags_has_products”、“bid_price_has_product”、“phone_has_user”。
我尝试删除这样的产品（php）： mysql_query("DELETE FROM Product WHERE id='$id'"); 我收到 1054 错误，这是外键错误。 这里的最佳实践是什么？据我了解，除了父表之外，我不需要在任何其他表中进行删除？
谢谢！
I am trying to understand relationships fully, and have gone through alot of tutorials. Still i linger on a few things (using MySQLWORKBENCH):
1. Every user can upload a product
2. Every product can have multiple categories, bids, ratings, etc
3. Every user can have multiple phonenumbers
(there are more, but this is the basic setup)
Is this correct?:
1 - I used a 1:n relationship, since every user can upload multiple products.
2 and 3. I used n:m relationship, since there can be multiple products with multiple categories, bids, ratings, etc.
DELETE/UPDATE:
I used ON UPDATE CASCADE and ON DELETE CASCADE everywhere where there is a foreign key...that being 'product', 'category_tags_has_products', 'bid_price_has_product', 'phone_has_user'.
I tried to delete a product like this (php): mysql_query("DELETE FROM product WHERE id='$id'");
I get a 1054 error, which is a foreign key error.
What are the best practises here? It is to my understanding that i shouldn't need to do deletions in any other than the parent-table?
Thanx!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您有很多标识关系，这意味着外键构成第二个表上主键的一部分。这在大多数情况下是不必要的，并且仅在诸如链接表之类的情况下真正有用。
为此，我会将 user->product 链接更改为非识别性，这将使 user_id 成为外键而不是主键的一部分。在工作台中，1:n 非识别关系的快捷方式是键“2”（其中“4”是识别键）。这反过来应该从产品链接到的表中删除 user_id 字段。
当您删除产品时，它应该级联到它链接到的 3 个链接表。目前，它可能正在尝试从用户中删除，这也取决于 FK 的设置方式。以下应该级联删除（假设删除是永久性的，并且您只想清除所有链接的记录）
DELETE FROM Product ->从任何具有product_id的表中删除
从用户中删除 ->从任何具有 user_id 的表中删除 这
同样适用于电话、评级、bid_price、category_tags。
我希望这有用，如果您需要任何进一步的指导，请随时喊:)
You have a lot of identifying relationships, which mean that the foreign key form part of the primary key on the second table. This is not necessary in most instances, and is only really useful in instances such as link tables.
To this end I would change the user->product link to be non-identifying, which will make user_id a Foreign Key instead of being part of the Primary Key. In workbench the shortcut for a 1:n non-identifying relationship is key '2' (where as '4' is identifying). This in turn should remove the user_id fields from the tables which product links onto.
When you delete a product, it should cascade to the 3 link tables that it links to. At present it may be that it is trying to delete from users also depending on how the FK is set up. The following should cascade deletions (assuming a deletion is permanent and you just want to clear out all linked records)
DELETE FROM product -> deletes from any table with product_id in
DELETE FROM user -> deletes from any table with user_id in
The same applies for phone, rating, bid_price, category_tags.
I hope this if of use, if you need any further pointers feel free to shout :)
这些关系看起来是正确的。
要找出查询出了什么问题，请检查
$id变量。或者检查整个查询，然后在控制台/phpMyAdmin/etc 中运行它：
PS：并且不要忘记转义从用户获得的所有数据！像这样：
The relations look correct.
To find out what is wrong with your query check the
$idvariable.Or check the whole query and then run it in the console/phpMyAdmin/etc:
P.S.: and don't forget to escape all data got from the users! Like this: