errno 150 in MySQL can't create table

Question

getting this error but cannot see the forest for the trees.

my product table:

CREATE TABLE IF NOT EXISTS `tblProducts` (
  `productID` int(11) NOT NULL AUTO_INCREMENT,
  `partNo` int(11) NOT NULL,
  `productName` varchar(50) NOT NULL,
  `description` mediumtext,
  `unitPrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `supplierPrice` decimal(10,2) DEFAULT '0.00',
  `marginDiscount` double(8,0) NOT NULL DEFAULT '0',
  `supplierID_FK` int(11) NOT NULL,
  `categoryID_FK` int(11) NOT NULL,
  `quantityPerUnit` varchar(20) NOT NULL DEFAULT '1',
  `unitsInStock` smallint(2) DEFAULT '0',
  `unitsOnOrder` smallint(2) DEFAULT '0',
  `reorderLevel` smallint(2) DEFAULT '0',
  `discontinued` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`productID`),
  KEY `ix_partNo` (`partNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

and here is the table I'm unable to create:

CREATE TABLE IF NOT EXISTS `tblProductAudit` (
  `productAuditID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `partNo` int(11) unsigned NOT NULL,
  `changeType` enum('NEW','EDIT','DELETE') NOT NULL,
  `changeTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`productAuditID`),
  KEY `ix_part_id` (`partNo`),
  KEY `ix_changeType` (`changeType`),
  KEY `ix_changeTime` (`changeTime`),
CONSTRAINT `FK_audit_partNo_id` FOREIGN KEY (`partNo`) REFERENCES `tblProducts` (`productID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

possible duplicate of [http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150](http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150). Tip: foreign keys constraint is improperly formed, maybe, types mismatch or smth like that. — ankhzet, Dec 02 '15 at 02:10

score 0 · Answer 1 · answered Dec 02 '15 at 02:08

0

tblProducts.ProductID field is a signed integer field, while the referencing PartNo field is unsigned. This is a type mismatch. I would also encourage you to use the same charset in both tables to avoid confusion.

answered Dec 02 '15 at 02:08

Shadow

33,525
10
51
64

errno 150 in MySQL can't create table

1 Answers1