MySQL 如何在表上添加 ON DELETE 约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4688599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I add ON DELETE constraint on the table?
提问by user505880
How can I add ON DELETE constraint on the table?
如何在表上添加 ON DELETE 约束？
回答by Pavel Dubinin
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`
回答by Caampa
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.
然后，打开 database.sql 文件并查找要更改的表。
Add ON DELETE CASCADEat the end of the foreign key sentence and save it.
ON DELETE CASCADE在外键句末尾添加并保存。
Second, import your database with the modifications:
其次，通过修改导入您的数据库：
$ mysql -u <user> -p <namedatabase> < database.sql
And you will have your ON DELETE CASCADE working.
您将使您的 ON DELETE CASCADE 工作。
