如何在表上添加删除约束?

How can I add ON DELETE constraint on the table?

如何在表上添加删除约束?

2 个解决方案

#1

Use ALTER TABLE+ADD CONSTRAINT. E.g. if you want to link tables members and profiles by member_id and cascade delete profiles each time the member is deleted, you can write something like this:

使用ALTER TABLE +添加约束。例如，如果你想通过member_id链接表成员和概要文件，并在每次删除成员时级联删除概要文件，你可以这样写:

ALTER TABLE profiles
   ADD CONSTRAINT `fk_test`
   FOREIGN KEY (`member_id` )
   REFERENCES `members` (`member_id` )
   ON DELETE CASCADE

If you will need to update that constraint - you'll have to remove it at then create again, there's no direct way to alter it.

如果您需要更新这个约束——您必须在删除它之后再创建它，那么没有直接的方法来修改它。

ALTER TABLE profiles DROP FOREIGN KEY `fk_test`

#2

If the foreign key is already created, there is a trick that worked for me. You can modified the dump of your database and import it again with the modifications.

如果外键已经创建，那么有一个技巧对我有用。您可以修改数据库的转储并通过修改再次导入它。

If you are using Mysql and Linux shell, it'd be like this:

如果你使用的是Mysql和Linux shell，它会是这样的:

First, export your database:

首先,导出数据库:

$ mysql -u <user> -p <namedatabase> > database.sql

Then, open the database.sql file and look for the table you want to alter.

然后,打开数据库。查找要修改的表。

Add ON DELETE CASCADE at the end of the foreign key sentence and save it.

在外键句末尾添加删除级联并保存。

Second, import your database with the modifications:

第二，导入修改后的数据库:

$ mysql -u <user> -p <namedatabase> < database.sql

And you will have your ON DELETE CASCADE working.

你会让你的删除级联工作。