I've been hinted into giving foreign keys a go as I'm trying to get better at database programming. The problem was, if a job in the jobs table was deleted, it left orphan messages relating to it in the messages table. So foreign keys were introduced to me to be the solution.
我一直暗示要为外键提供支持,因为我正努力在数据库编程方面做得更好。问题是,如果删除了作业表中的作业,它会在消息表中留下与其相关的孤立消息。所以外键被介绍给我作为解决方案。
However, I've gotten this when trying to do it, and I'm not sure what it's saying, plus, I'm not even sure which way round I should be setting the restriction, from the job to the messages, or vice versa. :(
但是,我在尝试这样做时已经得到了这个,而且我不确定它在说什么,而且,我甚至不确定我应该设置限制,从作业到消息,或者副作用反之亦然。 :(
Here's the error:
这是错误:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`nzua9c8_tasks`.<result 2 when explaining filename '#sql-2929_701930'>, CONSTRAINT `#sql-2929_701930_ibfk_1` FOREIGN KEY (`id`) REFERENCES `jobs` (`id`) ON UPDATE NO ACTION)
Also, the interface for PHPMyAdmin suggested that when I delete a task, the message ID would be "restricted". I didn't have an option for delete. I want any messages with a "job_id" that matches an id in the jobs table to be deleted too.
此外,PHPMyAdmin的界面建议当我删除任务时,消息ID将被“限制”。我没有删除选项。我想要删除任何与job表中的id匹配的“job_id”的消息。
Thanks for any help offered.
感谢您提供的任何帮助。
3 个解决方案
#1
2
You want to add on delete cascade to your foreign key definition (just after on update no action in your script).
您希望将删除级联添加到外键定义(刚更新后,脚本中没有操作)。
So it would be something like:
所以它会是这样的:
alter table messages add foreign key (id) references jobs (id) on delete cascade
More can be found here.
更多可以在这里找到。
#2
0
If one job contains several messages (1:N relation) then message table should have foreign key to the jobs table.
如果一个作业包含多个消息(1:N关系),则消息表应具有作业表的外键。
Your error message basically says that you cant update/delete row when there is something referencing it by foreign constraint.
您的错误消息基本上表示当有外部约束引用它时,您无法更新/删除行。
So if table jobs have records:
因此,如果表作业有记录:
id name
1 job1
2 job2
And table of messages have records
并且消息表有记录
id name job_id
1 mes1 1
2 mes2 2
Then you cant just drop record from first table. You should at first update referencing record in message table.
然后你不能从第一张表中删除记录。您应该首先更新消息表中的引用记录。
Also, you can change foreign key policy in such way that dependant records will be destroyed/updated automatically.
此外,您可以更改外键策略,以便自动销毁/更新相关记录。
#3
0
Foreign keys with corresponding "constraints" are for ensuring "referential integrity" and will solve the "dangling reference" problem you mentioned.
具有相应“约束”的外键用于确保“参照完整性”并且将解决您提到的“悬空参考”问题。
The "downside" to foreign keys is that the data must be populated into the database in the correct order:
外键的“缺点”是必须以正确的顺序将数据填充到数据库中:
If the rows in table "A" contain a foreign key pointer to one or more rows in table "B" then the rows in table "B" must be created before the rows in Table "A" can be created - otherwise the constraint will throw an error.
如果表“A”中的行包含指向表“B”中一行或多行的外键指针,那么必须先创建表“B”中的行,然后才能创建表“A”中的行 - 否则约束将抛出一个错误。
#1
2
You want to add on delete cascade to your foreign key definition (just after on update no action in your script).
您希望将删除级联添加到外键定义(刚更新后,脚本中没有操作)。
So it would be something like:
所以它会是这样的:
alter table messages add foreign key (id) references jobs (id) on delete cascade
More can be found here.
更多可以在这里找到。
#2
0
If one job contains several messages (1:N relation) then message table should have foreign key to the jobs table.
如果一个作业包含多个消息(1:N关系),则消息表应具有作业表的外键。
Your error message basically says that you cant update/delete row when there is something referencing it by foreign constraint.
您的错误消息基本上表示当有外部约束引用它时,您无法更新/删除行。
So if table jobs have records:
因此,如果表作业有记录:
id name
1 job1
2 job2
And table of messages have records
并且消息表有记录
id name job_id
1 mes1 1
2 mes2 2
Then you cant just drop record from first table. You should at first update referencing record in message table.
然后你不能从第一张表中删除记录。您应该首先更新消息表中的引用记录。
Also, you can change foreign key policy in such way that dependant records will be destroyed/updated automatically.
此外,您可以更改外键策略,以便自动销毁/更新相关记录。
#3
0
Foreign keys with corresponding "constraints" are for ensuring "referential integrity" and will solve the "dangling reference" problem you mentioned.
具有相应“约束”的外键用于确保“参照完整性”并且将解决您提到的“悬空参考”问题。
The "downside" to foreign keys is that the data must be populated into the database in the correct order:
外键的“缺点”是必须以正确的顺序将数据填充到数据库中:
If the rows in table "A" contain a foreign key pointer to one or more rows in table "B" then the rows in table "B" must be created before the rows in Table "A" can be created - otherwise the constraint will throw an error.
如果表“A”中的行包含指向表“B”中一行或多行的外键指针,那么必须先创建表“B”中的行,然后才能创建表“A”中的行 - 否则约束将抛出一个错误。