CODEWITHSUNDEEP
mysqlschemaddl
Dannyboy
Dannyboy

Reputation: 2052

mysql. can't create schema. I get this error: ERROR 1005 (HY000): (errno: 150)

I'm attempting to create a simple schema, see below. But for some reason I'm get this weird 150 error. I've triple-checked the schema and I really don' see the problem. Could you clarify what am I doing wrong?

DROP DATABASE IF EXISTS test222;
CREATE DATABASE IF NOT EXISTS test222 CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test222;

CREATE TABLE im_savegroups (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sgcode VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (sgcode)
) ENGINE=InnoDB CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE im_savespecs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fk_im_savegroups_sgcode VARCHAR(20) NOT NULL,
    sscode VARCHAR(20) NOT NULL,
    max_w INT UNSIGNED,
    max_h INT UNSIGNED,
    ratio_x INT UNSIGNED,
    ratio_y INT UNSIGNED,
    quality INT UNSIGNED,
    format VARCHAR(10),
    rel_dir VARCHAR(400),
    is_retina TINYINT UNSIGNED DEFAULT 0,
    is_preferred TINYINT UNSIGNED DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (fk_im_savegroups_sgcode) REFERENCES im_savegroups (sgcode) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY (sscode)
) ENGINE=InnoDB CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE im_originals (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fk_im_savespecs_sscode VARCHAR(20) NOT NULL,
    name VARCHAR(255),
    alt VARCHAR(180),
    filename VARCHAR(64),
    caption VARCHAR(1024),
    credit VARCHAR(1024),
    expires_at DATETIME,
    created_at DATETIME,
    updated_at DATETIME,
    PRIMARY KEY (id),
    FOREIGN KEY (fk_im_savespecs_sscode) REFERENCES im_savespecs (sscode) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

Here's the actual error I get:

ERROR 1005 (HY000): Can't create table 'test222.im_originals' (errno: 150)

Upvotes: 1

Views: 136

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Removing this part:

ON DELETE SET NULL

from line:

FOREIGN KEY (fk_im_savespecs_sscode) REFERENCES im_savespecs (sscode) 
ON DELETE SET NULL ON UPDATE CASCADE

of CREATE TABLE im_originals fixed the problem on my end.

Field fk_im_savespecs_sscode is NOT NULL, so rule ON DELETE SET NULL doesn't seem to make sense in this case.

Upvotes: 1

Related Questions