CODEWITHSUNDEEP
mysql
siannone
siannone

Reputation: 6763

MYSQL: Cannot add foreign key constraint

I've read many of the questions that talk about this problem but none seem to solve mine.

Basically, i receive the following error:

Error Code: 1215. Cannot add foreign key constraint

when trying to execute the following query:

CREATE TABLE IF NOT EXISTS Privacy (
    Biography varchar(20),
    BirthDate varchar(20),
    Email varchar(20),
    Location varchar(20),
    RealName varchar(20),
    Sex varchar(6),
    User varchar(255),
    PRIMARY KEY (User),
    FOREIGN KEY (User) REFERENCES Users(Username)
    ON DELETE SET NULL ON UPDATE CASCADE
) CHARACTER SET utf8;

I thought I couldn't have a primary key and a foreign on the same column, but then I made some searches and found out that it's perfectly fine. Maybe I'm just doing it in the wrong way.

Edit:

This is the Users table:

CREATE TABLE IF NOT EXISTS Users ("
    Avatar varchar(255),
    Biography text,
    Birth_Date date,
    Email varchar(255),
    Location varchar(255),
    Password varchar(20),
    Profile_Views int(11),
    Real_Name varchar(255),
    Reputation int(11),
    Signup_Date datetime,
    Username varchar(255),
    PRIMARY KEY (Username)
) CHARACTER SET utf8;

Upvotes: 1

Views: 72

Answers (2)

vadchen
vadchen

Reputation: 1462

The reason is ON DELETE SET NULL ON UPDATE CASCADE line.

If you use RESTRICT MySQL will be happy.

It makes sense, since you can not make primary key NULL once you delete user from Users table.

Edit: you can use ON DELETE CASCADE as there were pointed out in the comments, then it will delete the entire row from Privacy.

Upvotes: 3

Maulik patel
Maulik patel

Reputation: 2432

CREATE TABLE IF NOT EXISTS Privacy (
Biography varchar(20),
BirthDate varchar(20),
Email varchar(20),
Location varchar(20),
RealName varchar(20),
Sex varchar(6),
User varchar(255),
PRIMARY KEY (User),
FOREIGN KEY (User) REFERENCES Users(User)
)

Upvotes: -1

Related Questions