SQL Error #1215 - Cannot add foreign key constraint

Question

I have spent a good 3 hours searching for the answer to my problem (on here and google) and I cannot understand why I am getting this error after I try to add the "events" table.

Two tables:

    CREATE TABLE user (
            id INT NOT NULL AUTO_INCREMENT,
            u_name VARCHAR(32) NOT NULL,
            u_pass VARCHAR(32) NOT NULL,
            sub_status INT(3) NOT NULL,
            f_name VARCHAR(32),
            l_name VARCHAR(32),
            email VARCHAR(32),
            PRIMARY KEY(id, u_name, email)
    ) ENGINE=InnoDB;

    CREATE TABLE events (
        u_name VARCHAR(32) NOT NULL,
        event_name VARCHAR(32),
        event_date VARCHAR(32),
        comment VARCHAR(255),
        PRIMARY KEY(u_name, event_name),
        FOREIGN KEY(u_name) REFERENCES user(u_name)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    ) ENGINE=InnoDB;

As far as I can see, there are no spelling errors, u_name is the exact same type between tables and it is a primary key in both tables.

mysql version is 5.6

Help is much appreciated and I thank you in advance. Let me know if there is any else I need to include.

EDIT: Turns out my queries do work, but on a different version of sql/mysql. jemz's answer works as well for a different version of sql.

jemz · Accepted Answer · 2014-11-14 05:00:09Z

1

CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `u_name` VARCHAR(32) NOT NULL,
    `u_pass` VARCHAR(32) NOT NULL,
    `sub_status` INT(3) NOT NULL,
    `f_name` VARCHAR(32) NULL DEFAULT NULL,
    `l_name` VARCHAR(32) NULL DEFAULT NULL,
    `email` VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`, `u_name`, `email`),
    INDEX `u_name` (`u_name`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;


CREATE TABLE `events` (
    `u_name` VARCHAR(32) NOT NULL,
    `event_name` VARCHAR(32) NOT NULL DEFAULT '',
    `event_date` VARCHAR(32) NULL DEFAULT NULL,
    `comment` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`u_name`, `event_name`),
    CONSTRAINT `FK_events_user` FOREIGN KEY (`u_name`) REFERENCES `user` (`u_name`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

answered Nov 14, 2014 at 5:00

jemz

5,20319 gold badges69 silver badges109 bronze badges

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

2 Comments

seriouslyttt Over a year ago

Thank you jemz for answering so quickly. Though your tables work fine, I am curious about why mine didn't work? I was following my lecture slides 100% and still could not get it to work. Thank you again.

jemz Over a year ago

add unique index so that you can add foreign keys...please accept my answer if this solve your problem.

Collectives™ on Stack Overflow

SQL Error #1215 - Cannot add foreign key constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related