CODEWITHSUNDEEP
mysqldatabaseforeign-keysinnodb
user1679724
user1679724

Reputation: 21

Cant create table due to foreign key

I can't seem to create the third table here. Whats going on? I get the generic cant create table errno 150 message. Seems to have something to do with the foreign key

Table recipe

CREATE TABLE recipe(
    recipe_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    description VARCHAR(40) NOT NULL,
    PRIMARY KEY (recipe_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Table ingredient_type

CREATE TABLE ingredient_type(
    ingredient_type_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ingredient_type VARCHAR(40) NOT NULL,
    description VARCHAR(40) NOT NULL,
    PRIMARY KEY (ingredient_type_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Table ingredient

CREATE TABLE ingredient(
    ingredient_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ingredient_type_id INT(10) NOT NULL,
    name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    FOREIGN KEY (ingredient_type_id) REFERENCES ingredient_type (ingredient_type_id),
    PRIMARY KEY (ingredient_id)
)
ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Upvotes: 1

Views: 96

Answers (1)

user1233508
user1233508

Reputation:

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

Source: FOREIGN KEY Constraints in the MySQL manual

The problem in your code is that ingredient_type.ingredient_type_id is unsigned, but ingredient.ingredient_type_id is not.

Upvotes: 1

Related Questions