Hello everyone, can someone tell me where the error is. There are two tables ...

DROP TABLE IF EXISTS `products`; DROP TABLE IF EXISTS `product_categories`; CREATE TABLE IF NOT EXISTS `product_categories` ( `id` smallint unsigned NOT NULL AUTO_INCREMENT, `parent_id` smallint unsigned NOT NULL, `position` smallint unsigned NOT NULL, `left` smallint unsigned NOT NULL, `right` smallint unsigned NOT NULL, `level` smallint unsigned NOT NULL, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `products` ( `productID` smallint unsigned NOT NULL AUTO_INCREMENT, `catID` smallint unsigned NOT NULL, `productName` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `productInfo` text COLLATE utf8_unicode_ci NOT NULL, `productPrice` float(7,2) DEFAULT NULL, PRIMARY KEY (productID), FOREIGN KEY (catID) REFERENCES `product_categories` (parent_id) ON UPDATE RESTRICT ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

If we execute this code, we get an error ...

#1005 - Can't create table 'products' (errno: 150)

What's wrong?

Thank you in advance!

    1 answer 1

    Try to create the 2nd table like this:

     CREATE TABLE IF NOT EXISTS `products` ( `productID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `catID` SMALLINT UNSIGNED NOT NULL, `productName` VARCHAR(250) NOT NULL DEFAULT '', `productInfo` TEXT NOT NULL, `productPrice` FLOAT(7,2) DEFAULT NULL, PRIMARY KEY (productID), INDEX catID (catID), CONSTRAINT fk_products_catID FOREIGN KEY (catID) -- Тут была ошибка REFERENCES `product_categories` (parent_id) ON UPDATE RESTRICT ON DELETE CASCADE ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

    For fields productName and productInfo COLLATE is optional. The default will be the one after the bracket.

    • tried, also does not work. I don’t know if this can be considered an answer. Most likely this is a new question, but is it possible so ... I added an index to the table product_categories INDEX (parent_id) and it all worked without errors. - bruiser
    • Well yes, that's right. A foreign key cannot exist without an index. This, by the way, should have been written in the text of the error. - KiTE