MYSQL: Cannot add foreign key constraint

Question

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;

I'm not sure using the username as a primary key would be the thing to do here. I would advice adding an id in your users table (autoincrement) and referencing that. Nothing to do with your error probably btw — Nanne
– Nanne, Commented Mar 6, 2014 at 12:27
Having the PK of a table be an FK suggests that the referencing table's columns could simply be appended to the referenced table... — eggyal
– eggyal, Commented Mar 6, 2014 at 12:41

vadchen · Accepted Answer · 2014-03-06 13:01:49Z

3

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.

edited Mar 6, 2014 at 13:01

answered Mar 6, 2014 at 12:33

vadchen

1,4621 gold badge11 silver badges15 bronze badges

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

7 Comments

vadchen Over a year ago

@eggy Right, I'll add it as a note.

siannone Over a year ago

Well, that would be odd since I've got similar queries that create other tables and they are working...

Alma Do Over a year ago

Huh. My bad. Case-sensitive naming in *nix

eggyal Over a year ago

@ShadowBroker: Are the FKs also PK (which imposes the not NULL constraint) in the other tables? One assumes not, since you originally thought that might be the root of your problem.

vadchen Over a year ago

@AlmaDo I think you can change it in mysql settings regardless your OS: variable lower_case_table_names

|

Maulik patel · Accepted Answer · 2014-03-06 12:27:41Z

-1

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)
)

answered Mar 6, 2014 at 12:27

Maulik patel

2,4502 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

MYSQL: Cannot add foreign key constraint

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related