我的数据库的外键问题
我正在创建一个社交网站,并在朋友桌上工作,但不知道我做错了什么。
我有一个users
(userId,name - userId是主键)和另一个名为friends
(friend1,friend2,status)的表。 friend1
和 friend2
是更新和删除时 userId
的外键,设置为级联 表 users 有一个条目,其中的 ID 为 134,我想将其更改为 3。我只是想看看这个东西是如何工作的。 Friends 表中有两行的值为 143。如果我将 134 更改为 3,它不应该级联更新并更改 Friends 表中的这些值吗?我收到此错误
1451 - 无法删除或更新父行:外键约束失败 (modionz1_nightspot/friends
, CONSTRAINT friends_ibfk_1
FOREIGN KEY (friend1
code>) REFERENCES users
(userId
))
顺便说一下,这两个表都是 innodb 的。对这些概念的任何帮助将不胜感激。
I am creating a social networking site and am working on a friends table and dont know what Im doing wrong.
I have a users
(userId, name - userId is primary key) and another table called friends
(friend1, friend2, status). friend1
and friend2
are foreign keys to userId
on update and delete are set to cascade
table users has an entry with 134 in it as an Id and I want to change it to 3. I am just messing around to see how this stuff works. There are two rows with the value of 143 in the friends table. If I change 134 to 3 shouldnt it cascade on the update and also change those values in the friends table. I am getting this error
1451 - Cannot delete or update a parent row: a foreign key constraint fails (modionz1_nightspot/friends
, CONSTRAINT friends_ibfk_1
FOREIGN KEY (friend1
) REFERENCES users
(userId
))
both the tables are innodb by the way. Any help on these concepts would be greatly appreciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
首先,对于任何期望拥有超过数百条记录的数据库来说,级联更新都是一个非常糟糕的主意。如果您使用它并且您有数千或数百万条子记录,那么您可以将整个系统锁定几个小时。避免级联更新。正确的过程是添加您想要的父记录。然后更新子记录以反映该值而不是初始值,然后删除旧的父记录。然而,最好的设计是不需要更新 PK,这也是许多人使用代理键的原因之一。
In the first place cascade update is a very bad idea on any database that expects to have more than a couple of hundred records. If you use it and you have thousands or millions of child records then you can lock up the entire system for hours. Avoid cascade update. The correct process is to add the parent record you want. Then update the child records to reflect that value instead of the intial one and then delte the old parent record. Hoever it is best to design where you don't ever need to update the PK which is one reason why many people use surrogate keys.