如何使用命令行更改 MySQL 表的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11073706/
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 to alter a MySQL table's foreign key using the command line
提问by el ninho
How to alter an existing table in MySQL, setting foreign key to another table, using the command line?
如何使用命令行更改 MySQL 中的现有表，将外键设置为另一个表？
回答by WojtekT
You have to drop existing foreign keyand create another one. For example like this:
您必须删除现有foreign key并创建另一个。例如像这样：
ALTER TABLE my_table DROP FOREIGN KEY my_key;
ALTER TABLE my_table ADD CONSTRAINT my_key FOREIGN KEY ('some_id')
REFERENCES some_new_table ('some_other_id') ON UPDATE CASCADE ON DELETE CASCADE;
回答by Ravinder Reddy
Execute help alter tableat mysqlcommand prompt and the output is very much self explanatory.
Look for add constraintwith foreign keyclause and apply it on your table.
help alter table在mysql命令提示符下执行，输出非常不言自明。
查找add constraintwithforeign key子句并将其应用到您的桌子上。
mysql> help alter table
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_option ...
回答by sokoya_philip
I was able to achieve the same thing by :
我能够通过以下方式实现同样的目标：
ALTER TABLE the_table_name
ADD CONSTRAINT the_name_of_column_you_want_to_use_as_foreign_key
REFERENCES other_table_primary_id (Column_name )
回答by Alex W
If you have multiple foreign keys chained together and you get an error that ends with errorno 15xit most likely means that there are other tables that are dependent on the foreign key that you're trying to drop.
如果您将多个外键链接在一起，并且您收到以它结尾的错误，errorno 15x这很可能意味着还有其他表依赖于您尝试删除的外键。
To drop the foreign key when you get that error, you will need to do SET FOREIGN_KEY_CHECKS = 0;and then you must first drop the foreign keys on the tables that don't have any other tables dependent on them. You can then successfully drop the foreign keys on the next table up in the chain and so on.
要在收到该错误时删除外键，您需要执行以下操作SET FOREIGN_KEY_CHECKS = 0;，然后您必须首先删除没有任何其他表依赖的表上的外键。然后，您可以成功地删除链中下一个表上的外键，依此类推。
When you're done, make sure you run SET FOREIGN_KEY_CHECKS = 1;again.
完成后，请确保SET FOREIGN_KEY_CHECKS = 1;再次运行。
