CODEWITHSUNDEEP
mysqlsqldatabaserdbms
Utku Ufuk
Utku Ufuk

Reputation: 350

MySQL Error (HY000): Cannot add foreign key constraint

I'm trying to create a MySQL schema for a simple online shop. My existing tables are as follows:

I've created these tables using the following SQL code:

CREATE TABLE user(
    user_id INT AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL, 
    middle_name VARCHAR(20) NOT NULL, 
    last_name VARCHAR(20) NOT NULL, 
    email VARCHAR(40) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, 
    registered BOOLEAN NOT NULL, 
    phone VARCHAR(12),
    PRIMARY KEY(user_id)
);

CREATE TABLE product(
    product_id INT AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL UNIQUE,
    price DECIMAL(5,2) NOT NULL,
    description VARCHAR(100) UNIQUE,
    PRIMARY KEY(product_id)
);

CREATE TABLE address(
    user_id INT,
    address_id INT,
    city VARCHAR(15) NOT NULL,
    district VARCHAR(20) NOT NULL,
    details VARCHAR(50) NOT NULL,
    is_default BOOLEAN NOT NULL,
    FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE CASCADE,
    PRIMARY KEY(user_id, address_id)
);

CREATE TABLE purchase(
    purchase_id INT AUTO_INCREMENT,
    user_id INT,
    total_price DECIMAL(8,2) NOT NULL,
    state ENUM('placed', 'paid', 'shipped', 'received', 'completed', 'cancelled') DEFAULT 'placed',
    user_name VARCHAR(40) NOT NULL,
    full_address VARCHAR(85) NOT NULL,
    FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE SET NULL,
    PRIMARY KEY(purchase_id)
);

Now I'm trying to create a final table to store individual items in a shopping chart like this:

CREATE TABLE purchase_item(
    purchase_id INT,
    product_id INT,
    product_name VARCHAR(40) NOT NULL,
    amount DECIMAL(4,2) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    FOREIGN KEY(purchase_id) REFERENCES purchase(purchase_id) ON DELETE CASCADE,
    FOREIGN KEY(product_id) REFERENCES product(product_id) ON DELETE SET NULL,
    PRIMARY KEY(purchase_id, product_id)
);

But I get the following error:

ERROR 1215 (HY000): Cannot add foreign key constraint

I don't any errors if I update the foreign key for products as follows:

FOREIGN KEY(product_id) REFERENCES product(product_id) ON DELETE CASCADE,

I want the purchase_item to be deleted if the corresponding purchase gets deleted, but not when the corresponding product gets deleted.

Upvotes: 1

Views: 3197

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562428

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html says:

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

But your purchase_item.product_id column is part of that table's primary key, which implicitly makes the column NOT NULL.

You cannot use the SET NULL action for the primary key on that column.

Upvotes: 2

Related Questions