How to change MySQL table foreign key using command line

Question

How to modify an existing table in MySQL by setting the foreign key to another table using the command line?

Answer 1

You need to discard the existing foreign key and create another one. For example, for example:

 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; 

Answer 2

Run help alter table at the mysql command line, and the output is very obvious. Find add constraint with the foreign key clause and apply it in your table.

 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 ... 

Answer 3

I was able to achieve the same:

 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 ) 

Answer 4

If you have several foreign keys connected together and you get an error message that ends with errorno 15x , this most likely means that there are other tables that depend on the foreign key you are trying to delete.

To remove the foreign key, when you get this error, you will need to do SET FOREIGN_KEY_CHECKS = 0; and then you must first transfer the foreign keys to tables that have no other dependent tables. Then you can successfully delete the foreign keys in the next table in the chain, etc.

When you're done, make sure you run SET FOREIGN_KEY_CHECKS = 1; again SET FOREIGN_KEY_CHECKS = 1; .