MySQL Removing Some Foreign keys

Question

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

User · Answer

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

But the tricky part is that you can't drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:

SHOW CREATE TABLE region; This should show you a row ,at left upper corner click the +option ,the click the full text raio button then click the go .there you will get the name of the index, something like this:

CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION Now simply issue an:

alter table region drop foreign key region_ibfk_1;

or

more simply just type:- alter table TableName drop foreign key TableName_ibfk_1;

remember the only thing is to add _ibfk_1 after your tablename to make like this:- TableName_ibfk_1

User · Answer

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 · Answer

Here s a way to drop foreign key constraint  it will work  ALTER TABLE location location id  DROP FOREIGN KEY location ibfk 1

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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 · Answer

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

User · Answer

Try this   alter table Documents drop   FK  Documents  Custo  2A4B4B5E

User · Answer

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 · Answer

step1  show create table vendor locations   step2  ALTER TABLE vendor locations drop foreign key vendor locations ibfk 1   it worked for me

[mysql] MySQL Removing Some Foreign keys

Examples related to mysql

Examples related to foreign-keys

Examples related to constraints

Examples related to mysql-error-1025