MySQL error: Cannot add foreign key constraint (PrestaShop module)

I’m trying to write MySQL queries for my custom PrestaShop module. I encountered an issue while adding foreign keys and have no idea what might be wrong. I double checked column types and their existance.

Here’s the error:

Cannot add foreign key constraint

CREATE TABLE IF NOT EXISTS `ps_pp_project` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_group` INT,
    `id_product` INT,
    `name` VARCHAR(255),
    `height` INT,
    `width` INT,
    `file` VARCHAR(255),
    `class_name` VARCHAR(255),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_product`) REFERENCES `' ._DB_PREFIX_. '
    product`(`id_product`) ON DELETE CASCADE,
    FOREIGN KEY (`id_group`) REFERENCES `' ._DB_PREFIX_. 
    'pp_group`(`id`) ON DELETE CASCADE
) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8mb4';

And here is all the code I’m trying to run during installation

    $sqls[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'pp_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NULL,
    PRIMARY KEY (`id`)
) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8mb4';

$sqls[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'pp_project` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_group` INT,
    `id_product` INT(10),
    `name` VARCHAR(255),
    `height` INT,
    `width` INT,
    `file` VARCHAR(255),
    `class_name` VARCHAR(255),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_product`) REFERENCES `' ._DB_PREFIX_. 'product`(`id_product`)
    ON DELETE CASCADE,
    FOREIGN KEY (`id_group`) REFERENCES `' ._DB_PREFIX_. 'pp_group`(`id`)
    ON DELETE CASCADE
) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8mb4';

$sqls[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'pp_layer` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_project` INT,
    `label` VARCHAR(255) DEFAULT 'TwÃ³j tekst',
    `position_x` INT DEFAULT 0,
    `position_y` INT DEFAULT 0,
    `max_chars` INT DEFAULT 10,
    `text_align` VARCHAR(255) DEFAULT 'left',
    `height` INT DEFAULT 0,
    `width` INT DEFAULT 0,
    `placeholder` VARCHAR(255) DEFAULT 'Tekst',
    `rotation` INT DEFAULT 0,
    `skew_x` INT DEFAULT 0,
    `skew_y` INT DEFAULT 0,
    `color` VARCHAR(255) DEFAULT '000',
    `font_family` VARCHAR(255) DEFAULT 'arial',
    `font_size` INT DEFAULT 12,
    `line_height` INT DEFAULT 12,
    `row_quantity` INT DEFAULT 1,
    `visible` BOOL DEFAULT 1,
    `layer_type` VARCHAR(255) DEFAULT 'text',
    `file` VARCHAR(255) DEFAULT null,
    `opacity` FLOAT DEFAULT 1.0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_project`) REFERENCES `' ._DB_PREFIX_. 'pp_project`(`id`)
    ON DELETE CASCADE
) ENGINE=' . _MYSQL_ENGINE_ . ' DEFAULT CHARSET=UTF8mb4';


foreach ($sqls as $sql) {
    if (!Db::getInstance()->execute($sql)) {
        return false;
    }
}

The queries execute perfectly without foreign keys.

UPDATE

Found out that the issue is this line:

FOREIGN KEY (`id_product`) REFERENCES `' ._DB_PREFIX_. '
product`(`id_product`) ON DELETE CASCADE,

However, I haven’t yet figured out how to fix it.

Answer

I found the solution to the problem.

In the table ps_product column id_product is UNSIGNED and NOT NULL.

While creating table ps_pp_project I create column id_product without specifying those attributes

$sqls[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'pp_project` (
`id` INT NOT NULL AUTO_INCREMENT,
`id_group` INT,
`id_product` INT(10),
[...]

To solve the issue, the query should look like this:

$sqls[] = 'CREATE TABLE IF NOT EXISTS `' . _DB_PREFIX_ . 'pp_project` (
`id` INT NOT NULL AUTO_INCREMENT,
`id_group` INT,
`id_product` INT(10) UNSIGNED NOT NULL,
[...]

Advertisement

Answer