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

mysql alter-table alter
el ninho picture el ninho · Jun 17, 2012 · Viewed 38.7k times · Source

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

Answer

WojtekT picture WojtekT · Jun 17, 2012

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;

Related questions

How to delete a column from a table in MySQL

Given the table created using: CREATE TABLE tbl_Country ( CountryId INT NOT NULL AUTO_INCREMENT, IsDeleted bit, PRIMARY KEY (CountryId) ) How can I delete the column IsDeleted?

Read More
Error renaming a column in MySQL

How do I rename a column in table xyz? The columns are: Manufacurerid, name, status, AI, PK, int I want to rename to manufacturerid I tried using PHPMyAdmin panel, but I get this error: MySQL said: Documentation #1025 - Error on …

Read More
Alter a MySQL column to be AUTO_INCREMENT

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification. ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am …

Read More