外键的第一次体验，我做错了什么？-FirstexperienceofForeignkeys,what'veIdonewrong?

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.

更多可以在这里找到。

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.

此外,您可以更改外键策略,以便自动销毁/更新相关记录。

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”中的行 - 否则约束将抛出一个错误。