CODEWITHSUNDEEP
mysqlforeign-keys
puk
puk

Reputation: 16782

MySQL FOREIGN key not working

I posted a question here and as per the response I am trying to create the following MySQL table, but it does not work, however, if I remove the two foreign commands it works

$query="CREATE TABLE IF NOT EXISTS picture(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID)
)ENGINE=InnoDB";
mysql_query($query,$con);

$query="CREATE TABLE IF NOT EXISTS user(
ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID)
)ENGINE=InnoDB";
mysql_query($query,$con);

$query="CREATE TABLE IF NOT EXISTS pictureRating
(
ID INT NOT NULL AUTO_INCREMENT,
pictureID INT NOT NULL,
userID INT NOT NULL,
rater INT NOT NULL,
creationDate TIMESTAMP DEFAULT NOW(),
context VARCHAR(150),
rating TINYINT,

PRIMARY KEY (ID),
FOREIGN KEY (pictureID) REFERENCES picture(ID) ON UPDATE CASCADE,
FOREIGN KEY (userID) REFERENCES user(ID) ON UPDATE CASCADE
)ENGINE=InnoDB";
mysql_query($query,$con)

I can't figure out why the tables fail to get created

Upvotes: 1

Views: 521

Answers (1)

Mark Byers
Mark Byers

Reputation: 838256

You need to drop all three tables before you run your script.

It's not enough just to drop the pictureRating table. You could have old versions of the first two tables which could cause the creation of the third table to fail with the foreign key constraints, but succeed when you omit them.

When I delete all three tables and then try running your commands it works fine.

Upvotes: 3

Related Questions