Fixing SQL errno: 150

Description:

On table altering, SQL or phpMyAdmin throws an 150 error, with a message like Error on rename of ‘./tables/#sql-efc_1’ to ‘./tables/my_table’ (errno: 150)” or “Cannot add or update a child row: a foreign key constraint fails”.

Solution:

Error 150 is related to foreign key relations. Receiving this error means that a foreign key was defined in the table to be altered and it cannot be dropped as its properties are still valid.

What to do:

In phpMyAdmin, go to the table Structure and scroll down to find the “Relation view” link. Click on it and eliminate all the relations given to the table column you want to alter/drop.

In plain MySQL , just drop the foreign key definition, as follows:

ALTER TABLE my_table
DROP FOREIGN KEY fk_table_id;

In Oracle, or other query languages the constraint has to be eliminated :

ALTER TABLE my_table
DROP CONSTRAINT fk_table_id;

After the foreign key definition is eliminated, altering the table should work with no issues.

Advertisements

About admin

Just another php developer trying to give something back to the community.
This entry was posted in Best practices, SQL and tagged , , , . Bookmark the permalink.

2 Responses to Fixing SQL errno: 150

  1. gurmanialv says:

    You really think foreign keys are defined for no reason and dropping them would not affect data integrity and aplication logic?

    • admin says:

      Nobody suggested that. Foreign keys have great logic and are a big part of the data integrity. That’s why we get an error or a warning when we try to change some data without considering the foreign keys. This post only shows what to do when there is a foreign key constraint in the way of a table drop or such. Of course we assume that the administrator of the database has a reason for dropping a table, and he has considered all the possible impact.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s