Cannot add foreign key constraints, Mysql

Question

please could you please tell me what I am doing wrong? I have MySQl script but finally I got this

Error 1215: Cannot add foreign key constraints

Here is code:

   CREATE TABLE IF NOT EXISTS profile_public_data
(
    idProfile INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    login VARCHAR(64),
    name VARCHAR(45),
    lastName VARCHAR(45),
    location VARCHAR(45),
    is_active BOOLEAN DEFAULT false,
    age TINYINT,
    photo VARCHAR(45),
    PRIMARY KEY(idProfile)
);

CREATE TABLE IF NOT EXISTS profile_private_data
(
    idProfile INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT fk_idProfile 
            FOREIGN KEY(idProfile) 
            REFERENCES `profile_public_data`(idProfile)
                ON DELETE SET NULL 
                ON UPDATE SET NULL,
    email VARCHAR(45),
    password VARCHAR(45),
    PRIMARY KEY(idProfile)
);

I think you are trying to set NULL on DELETE and UPDATE but the column is NOT NULL — jeevan_23
– jeevan_23, Commented Jul 29, 2018 at 9:09

Jacob · Accepted Answer · 2018-07-29 09:26:34Z

1

You idProfile column in the dependent table is set as auto increment, this is not correct. It should be just INT(11).

Its value must be specified and exists in the source table when when inserting a new record, instead of auto incremented.

edited Jul 29, 2018 at 9:26

answered Jul 29, 2018 at 9:12

Jacob

1,96615 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot add foreign key constraints, Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related