I am getting this error when I am trying to run an alter table command to drop a column:
ERROR 1025 (HY000): Error on rename of …. (errno: 150).
If I understand correctly it is a foreign key problem, but I do not have a clue how to fix it. Would somebody be so kind and tell me how to get it working.
The code used for creating table:
CREATE TABLE categories( cid INT AUTO_INCREMENT NOT NULL PRIMARY KEY, assets_id INT NOT NULL, cat_name VARCHAR(30) NOT NULL, INDEX(assets_id), FOREIGN KEY (assets_id) REFERENCES asset(aid) ON UPDATE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;
The alter command:
ALTER TABLE categories DROP COLUMN assets_id;
The table categories is completely blank. So there is no information to set off the CASCADE restrictions. So could you help me what kind of wizardry do I need to delete the column assets_id. Thank you.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Use SHOW CREATE TABLE categories to show the name of constraint.
Most probably it will be categories_ibfk_1
Use the name to drop the foreign key first and the column then:
ALTER TABLE categories DROP FOREIGN KEY categories_ibfk_1; ALTER TABLE categories DROP COLUMN assets_id;
Method 2
For me the problem was a different one:
The site was (accidentally) accessible for everyone. So the update script was startet multiple times. That caused race conditions that threw errors like this.
-> Be sure that the site gets accessed only once, until every script finished!
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0