MySql Error 1025

Question

I'm trying to drop a foreign key column in a table I have named ProductInvoice. The column I'm trying to drop is named PersonID and is from the table Person. When I run the query

ALTER TABLE ProductInvoice
DROP COLUMN PersonID;

I get this error...

Error Code: 1025. Error on rename of './jkripal/#sql-91c_19ff0' to './jkripal/ProductInvoice' (errno: 150)

Any advice on how to troubleshoot this? I've looked around this site and can't find any answers that help.

These are the results from SHOW CREATE TABLE ProductInvoice

'ProductInvoice', 'CREATE TABLE `ProductInvoice` 
(\n  `ProductInvoiceID` int(11) NOT NULL AUTO_INCREMENT,
\n  `PersonID` int(11) DEFAULT NULL,
\n  `ProductID` int(11) NOT NULL,
\n  `InvoiceID` int(11) NOT NULL,
\n  `TravelDate` varchar(255) DEFAULT NULL,
\n  `TicketNote` varchar(255) DEFAULT NULL,
\n  `Quantity` int(11) DEFAULT NULL,
\n  `InsuranceTicketCode` varchar(255) DEFAULT NULL,
\n  PRIMARY KEY (`ProductInvoiceID`),
\n  KEY `fkPerson` (`PersonID`),
\n  KEY `fk_ProductInvoice_to_Product` (`ProductID`),
\n  KEY `fk_ProductInvoice_to_Invoice` (`InvoiceID`),
\n  CONSTRAINT `ProductInvoice_ibfk_1` FOREIGN KEY (`PersonID`) REFERENCES `Person` (`PersonID`),
\n  CONSTRAINT `ProductInvoice_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `Product` (`ProductID`),
\n  CONSTRAINT `ProductInvoice_ibfk_3` FOREIGN KEY (`InvoiceID`) REFERENCES `Invoice` (`InvoiceID`)
\n) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8'

Look here on how to find and drop the foreign key http://stackoverflow.com/a/19668812/1745672 — Mihai, Nov 07 '15 at 09:37

score 1 · Answer 1 · answered Nov 08 '15 at 09:12

First disable foreign keys

SET foreign_key_checks = 0;

Delete the key on PersonID

ALTER TABLE ProductInvoice
  DROP INDEX fkPerson;

Now drop the foreign key

ALTER TABLE ProductInvoice DROP FOREIGN KEY ProductInvoice_ibfk_1;

Drop the column

ALTER TABLE ProductInvoice DROP COLUMN PersonID;

Enable foreign keys:

SET foreign_key_checks = 1;

score 0 · Answer 2 · answered Nov 07 '15 at 09:31

0

Drop Foreign key first:

ALTER TABLE ProductInvoice DROP FOREIGN KEY ....;

and then:

ALTER TABLE ProductInvoice DROP COLUMN PersonID;

answered Nov 07 '15 at 09:31

Lukasz Szozda

162,964
23
234
275

I tried this as well and it won't work. I edited the original post to include the error. – Ryan Dorman Nov 07 '15 at 09:35
@RyanDorman You need to use **`FOREIGN KEY` name**. Not column name – Lukasz Szozda Nov 07 '15 at 09:36
It's been a while since I wrote these tables, is there a way to find this easily? – Ryan Dorman Nov 07 '15 at 09:38
I just found the name and edited it as you said, but for some reason it still will not erase the column. I edited the original post with the updated query and error that I recieve. – Ryan Dorman Nov 07 '15 at 09:42
@RyanDorman Drop Index after droping foreign key: `DROP INDEX fkPerson ON ProductInvoice;` – Lukasz Szozda Nov 07 '15 at 09:44
I tried this. The program fails on the first command, the drop foreign key query. – Ryan Dorman Nov 07 '15 at 09:50

MySql Error 1025

2 Answers2