Search code examples
mysqlforeign-keys

Rules to add the foreign key constraint

I'm learning database. I need to create tables in this picture in mysql

Relationship

This is the code I have in Jupyter notebook:

CREATE TABLE warehouse(
code VARCHAR(100) not null,
ISBN varchar(225),
number int,
address varchar(30),
phone varchar(20),
PRIMARY KEY(code)
);

CREATE TABLE book(
ISBN varchar(225),
title varchar(255),
year  decimal(4,0),
price decimal(10,2),
in_stock int,
in_basket int,
PRIMARY KEY(ISBN),
FOREIGN KEY (ISBN, in_stock) REFERENCES warehouse(ISBN, number)
);

CREATE TABLE shopping_basket(
basket_id varchar(40),
ISBN varchar(225),
number_in_basket int,
PRIMARY KEY(basket_id),
FOREIGN KEY (ISBN, number_in_basket) REFERENCES book(ISBN, in_basket)
 );

My error:

DatabaseError: (mysql.connector.errors.DatabaseError) 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'book_ibfk_1' in the referenced table 'warehouse'
[SQL: CREATE TABLE book(
    ISBN varchar(225),
    title varchar(255),
    year  decimal(4,0),
    price decimal(10,2),
    in_stock int,
    in_basket int,
    PRIMARY KEY(ISBN),
    UNIQUE KEY(in_stock),
    FOREIGN KEY (ISBN, in_stock) REFERENCES warehouse(ISBN, number)
    );]

Now I'm sure that they all have the same types of variables. Can anyone help me with this bug? I'm learning so I have limited understanding Thank you

Solution

  • All refrenced foreign keys needs an index, that is mandatory.

    So your code must look like.

    you are using a combined foreign key, so also the index must be combined

    CREATE TABLE warehouse(
code VARCHAR(100) not null,
ISBN varchar(225),
number int,
address varchar(30),
phone varchar(20),
PRIMARY KEY(code),
KEY(ISBN, number)
);

CREATE TABLE book(
ISBN varchar(225),
title varchar(255),
year  decimal(4,0),
price decimal(10,2),
in_stock int,
in_basket int,
PRIMARY KEY(ISBN),
FOREIGN KEY (ISBN, in_stock) REFERENCES warehouse(ISBN, number),
KEY (ISBN, in_basket)
);

CREATE TABLE shopping_basket(
basket_id varchar(40),
ISBN varchar(225),
number_in_basket int,
PRIMARY KEY(basket_id),
FOREIGN KEY (ISBN, number_in_basket) REFERENCES book(ISBN, in_basket)
 );