How to alter a MySQL table's foreign key using the command line

Question

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

I tried to use google, and it didn't find anything that covers this exact case. I know to do it in MySQL Administrator, or to create table with foreign key, but I don't know this in mentioned question. — el ninho, Jun 17 '12 at 18:22
Please note you can find the product documentation in Google with `mysql 5.7 alter table` . — Álvaro González, May 06 '16 at 09:27

score 20 · Answer 1 · answered Jun 17 '12 at 18:24

20

You have to drop existing foreign key and create another one. For example like this:

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;

answered Jun 17 '12 at 18:24

WojtekT

4,735
25
37

Your code works, but I have a related question. When I tried to combine those two queries into a single query, `ALTER TABLE my_table DROP ..., ADD ...` I got an error, _errno: 121 "Duplicate key on write or update"_. Am I missing something, or is it indeed impossible to combine those queries? – iloo Apr 24 '17 at 08:01

score 3 · Answer 2 · answered Jun 17 '12 at 18:26

Execute help alter table at mysql command prompt and the output is very much self explanatory.
Look for add constraint with foreign key clause and apply it on 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 ...

score 2 · Answer 3 · edited Aug 03 '14 at 12:58

2

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 )

edited Aug 03 '14 at 12:58

Loopo

2,204
2
28
45

answered Aug 03 '14 at 12:40

sokoya_philip

21
1

Alex W · Answer 4 · 2015-04-08T13:52:47.733

If you have multiple foreign keys chained together and you get an error that ends with errorno 15x it most likely means that there are other tables that are dependent on the foreign key that you're trying to drop.

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.

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

How to alter a MySQL table's foreign key using the command line

4 Answers4

Linked