krs8888 krs8888 - 1 year ago 85
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),

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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download