MySQL Removing Some Foreign keys

As explained here, seems the foreign key constraint has to be dropped by constraint name and not the index name.

The syntax is:

ALTER TABLE footable DROP FOREIGN KEY fooconstraint;

The foreign keys are there to ensure data integrity, so you can't drop a column as long as it's part of a foreign key. You need to drop the key first.

I would think the following query would do it:

ALTER TABLE assignmentStuff DROP FOREIGN KEY assignmentIDX;

As everyone said above, you can easily delete a FK. However, I just noticed that it can be necessary to drop the KEY itself at some point. If you have any error message to create another index like the last one, I mean with the same name, it would be useful dropping everything related to that index.

ALTER TABLE your_table_with_fk
  drop FOREIGN KEY name_of_your_fk_from_show_create_table_command_result,
  drop KEY the_same_name_as_above

Check what's the CONSTRAINT name and the FOREIGN KEY name:

SHOW CREATE TABLE table_name;

Remove both the CONSTRAINT name and the FOREIGN KEY name:

ALTER TABLE table_name
  DROP FOREIGN KEY the_name_after_CONSTRAINT,
  DROP KEY the_name_after_FOREIGN_KEY;

Hope this helps!

Hey I followed some sequence above, and found some solution.

SHOW CREATE TABLE footable;

You will get FK Constrain Name like

ProjectsInfo_ibfk_1

Now you need to remove this constraints. by alter table commantd

alter table ProjectsInfo drop foreign key ProjectsInfo_ibfk_1;

Then drop the table column,

alter table ProjectsInfo drop column clientId;

MySQL Removing Some Foreign keys

Related

Recent Posts