How to add foreign key to table in mysql

Question

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

You need to create the product and user1 tables before they can be referenced by foreign keys. Move the trans table to the last position. — Bill Karwin
– Bill Karwin, Commented Sep 23, 2016 at 2:00

Tim Biegeleisen · Accepted Answer · 2016-09-23 02:04:00Z

2

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.

answered Sep 23, 2016 at 2:04

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Petar Ivanov · Accepted Answer · 2016-09-23 02:01:30Z

1

What is order you create tables. You need first to create product and user1 and at the end - trans.

answered Sep 23, 2016 at 2:01

Petar Ivanov

514 bronze badges

Collectives™ on Stack Overflow

How to add foreign key to table in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related