I recently needed to make some database modifications that required removing some existing foreign key constraints. The command looks like this.

ALTER table tablename DROP FOREIGN KEY foreign_key_name

MySQL was throwing errors.

mysql> ALTER TABLE reviews DROP FOREIGN KEY priority_id;
ERROR 1025 (HY000): Error on rename of ‘./dbname/reviews’ to ‘./dbname/#sql2-62f5-145b’ (errno: 152)

If you run

SHOW CREATE TABLE tablename

You’ll see the constraint names. You can then delete the foreign key based on the constraint.

ALTER TABLE tablename DROP FOREIGN KEY `reviews_ibfk_24`;

You can then drop the column as needed.

In a recent project I needed to add a new table that would create a foreign key constraint on an existing table. I’m using the InnoDB storage engine. The existing table is employees and the new table is shifts. Shifts table is pretty simple.

CREATE TABLE shifts (
        id INT AUTO_INCREMENT NOT NULL,
        shift VARCHAR(15) NOT NULL,
        PRIMARY KEY(id)
) ENGINE=InnoDB;

The existing employees table needed a new column called “shift_id” that would reference the shifts table. The following code can be used to add the column and then add the foreign key constraint.

ALTER TABLE employees ADD shift_id INT AFTER group_id;
ALTER TABLE employees ADD CONSTRAINT FOREIGN KEY(shift_id) REFERENCES shifts(id) ON UPDATE CASCADE ON DELETE SET NULL;

The first alter statement worked fine, but the second kept throwing an error 150. You can view more details about the last error with the following:

SHOW INNODB STATUS;

I would see the following:

————————
LATEST FOREIGN KEY ERROR
————————
110805 8:49:16 Error in foreign key constraint of table backlogreviews/#sql-62f5_23:
FOREIGN KEY(shift_id) REFERENCES shifts(id) ON UPDATE CASCADE ON DELETE SET NULL:
Cannot resolve table name close to:
(id) ON UPDATE CASCADE ON DELETE SET NULL

This didn’t exactly help as I know the shifts table exists. I did some searching on google and found some hints, but these mostly indicated the column doesn’t match up correctly. Turns out I had a syntax error when creating the shifts table. The engine was INNOBD (notice the BD should read DB). Instead of throwing an error when creating the table it just used MyISAM. Since the storage engines didn’t match I was getting the error.