MySQL 和外键
我有一个关于 InnoDB 表中的外键的问题。我的数据库中有 4 个表：
Signup:
- UID - 主键
- Afid
- Planid
- Industryid
Affiliates:
- Afid - 主键
Plans:
- Planid - 主键
行业：
- Industryid - 主键
SQL 我用来添加键：
CONSTRAINT `FK_signup_industries` FOREIGN KEY (`industryid`) REFERENCES `industries` (`industryid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_affiliates` FOREIGN KEY (`afid`) REFERENCES `affiliates` (`afid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE CASCADE
我的问题是：
如果我要删除各自表上的计划或行业，用户会在注册中表被删除？我试图找到这方面的指南，但他们解释得不是很好。
基本上我需要做的是无论如何都不会删除注册表中的行。当我使用此查询时：
CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE NO ACTION
我收到此错误：
Cannot delete a parent row.
I have a question regarding Foreign Keys in an InnoDB Table. I have 4 tables in my database:
Signup:
- UID - Primary Key
- Afid
- Planid
- Industryid
Affiliates:
- Afid - Primary Key
Plans:
- Planid - Primary Key
Industries:
- Industryid - Primary Key
SQL I use to add the keys:
CONSTRAINT `FK_signup_industries` FOREIGN KEY (`industryid`) REFERENCES `industries` (`industryid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_affiliates` FOREIGN KEY (`afid`) REFERENCES `affiliates` (`afid`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE CASCADE
What my question is:
If I was to delete a plan or an industry on their respective tables would the user in the signup table be deleted? I have tried to find guides on this and they don't explain it very well.
Basically what I need done is that the row in the signup table never to be deleted no matter what. When I use this query:
CONSTRAINT `FK_signup_plans` FOREIGN KEY (`planid`) REFERENCES `plans` (`planid`) ON UPDATE CASCADE ON DELETE NO ACTION
I get this error:
Cannot delete a parent row.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您需要保留“注册”中的行，即使您删除了“行业”、“附属机构”或“计划”中引用的行，那么您有两种选择：
不声明外键约束。这消除了引用完整性的强制执行，但它允许 Signup 中存在引用不再存在的父主键值的行。
使用 ON DELETE SET NULL。这允许保留该行，但引用现在已删除父级的外键值将被更改。*
有关更多详细信息，请参见 http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
*标准SQL规范还定义了一条规则ON DELETE SET DEFAULT，但InnoDB不支持此功能。
If you need the row in Signup to stay even if you delete the referenced row in Industries, Affiliates, or Plans, then you have two choices:
Do not declare a foreign key constraint. This removes enforcement of referential integrity, but it allows rows to exist in Signup that reference a parent primary key value that no longer exists.
Use ON DELETE SET NULL. This allows the row to stay, but the foreign key value that references the now-deleted parent will be changed.*
For more details see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
* The standard SQL specification also defines a rule ON DELETE SET DEFAULT, but InnoDB doesn't support this feature.