MySQL Removing Some Foreign keys

user_67521 0
Viewed 387353

I have a table whose primary key is used in several other tables and has several foreign keys to other tables. 

CREATE TABLE location (
   locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   ...
) ENGINE = InnoDB;

CREATE TABLE assignment (
   assignmentID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   locationID INT NOT NULL,
   FOREIGN KEY locationIDX (locationID) REFERENCES location (locationID)
   ...
) ENGINE = InnoDB;

CREATE TABLE assignmentStuff (
   ...
   assignmentID INT NOT NULL,
   FOREIGN KEY assignmentIDX (assignmentID) REFERENCES assignment (assignmentID)
) ENGINE = InnoDB;

The problem is that when I'm trying to drop one of the foreign key columns (ie locationIDX) it gives me an error.

"ERROR 1025 (HY000): Error on rename"

How can I drop the column in the assignment table above without getting this error?

mysql-error-1025 constraints foreign-keys mysql
11 Answers

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;
user_770970

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;
user_812050

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;
user_58457390

You can not drop the foreign key column because it is being referenced from the table assignmentStuff. So you should first drop the foreign key constraint assignmentStuff.assignmentIDX.

A similar question has already been asked here. Check also here for more info.

user_235620

step1: show create table vendor_locations;

step2: ALTER TABLE vendor_locations drop foreign key vendor_locations_ibfk_1;

it worked for me.

user_101581110

first need to get actual constrain name by this query

SHOW CREATE TABLE TABLE_NAME

This query will result constrain name of the foreign key, now below query will drop it.

ALTER TABLE TABLE_NAME DROP FOREIGN KEY COLUMN_NAME_ibfk_1

last number in above constrain name depends how many foreign keys you have in table

user_57652280
Related