krs8888 krs8888 - 2 months ago 6
MySQL Question

How to add foreign key to table in mysql

What I am doing incorrect? Trying to create these tables in sqlfiddle
does not work gives

Cannot add foreign key constraint

create table product (
pid int NOT NULL,
name varchar(10),
PRIMARY KEY (pid)
);

create table trans (
tid int NOT NULL ,
productId int NOT NULL,
userId int NOT NULL,
PRIMARY KEY (tid),
FOREIGN KEY (productId) REFERENCES product(pid),
FOREIGN KEY (userId) REFERENCES user1(uid)
);

create table user1 (
uid int NOT NULL ,
location varchar(22),
PRIMARY KEY (uid)
);

Answer

As @BillKarwin mentioned, the definitions for tables containing primary keys referenced by the trans table should appear before the definition for the trans table. So you should move the definition for the trans table to last.

However, even doing this still results in an error in SQLFiddle:

SQLFiddle (uncomment the foreign key reference in trans)

SQLFiddle seems to have some sort of problem with accepting this table schema. This is not surprising, as the site seems to have such problems frequently.

Comments