如何在MySQL中使用命令行更改现有表,并将外键设置为另一张表?
您需要删除现有的外键
,并创建另一个。例如像这样:
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;
ALTER TABLE my_table DROP ..., ADD ...
我遇到了一个错误,_errno: 121 "Duplicate key on write or update"_。我是不是漏掉了什么,或者确实无法将这些查询组合起来? - iloo在mysql
命令提示符下执行help alter table
,输出结果非常自解释。
查找带有foreign key
子句的add constraint
并将其应用于您的表。
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 ...
errorno 15x
结尾的错误,则很可能意味着其他表依赖于您正在尝试删除的外键。SET FOREIGN_KEY_CHECKS = 0;
,然后必须首先删除没有任何其他表依赖的表上的外键。然后,您可以成功地删除链中上一个表上的外键,以此类推。SET FOREIGN_KEY_CHECKS = 1;
。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 )
mysql 5.7 alter table
的产品文档。 - Álvaro González