Foreign Key error in MySql

Question

im having issues with the below in mysql. i get an error (ERROR 1215: Cannot add foreign key constraint) using the workbench, but i don't know what the error is. Any help i would appreciate it.

{CREATE TABLE IF NOT EXISTS item (
itemNo CHAR(8),
categId CHAR(8),
sellerId CHAR(6),
itemName VARCHAR(30),
descr VARCHAR(20),
listPrice CHAR(8),
discPrice CHAR(8),
itemComment VARCHAR(30),
CONSTRAINT item_PK PRIMARY KEY(itemNo),
CONSTRAINT item_FK1 FOREIGN KEY(categId) REFERENCES category(categId),
CONSTRAINT item_FK2 FOREIGN KEY(sellerId) REFERENCES feedback(sellerId) 
);}

Here are the tables, the first two were created its the item table that im stuck on.

CREATE TABLE department (
  departId CHAR(6),
  depDesc VARCHAR(20),
  CONSTRAINT dep_PK PRIMARY KEY(departId)
);

CREATE TABLE category (
  categId CHAR(8),
  catDesc VARCHAR(20),
  departId VARCHAR(20),
  CONSTRAINT cat_PK PRIMARY KEY(categId),
  CONSTRAINT cat_FK FOREIGN KEY(departId) REFERENCES department(departId)
);

CREATE TABLE item (
  itemNo CHAR(8),
  categId CHAR(8),
  sellerId CHAR(6),
  itemName VARCHAR(30),
  descr VARCHAR(20),
  listPrice CHAR(8),
  discPrice CHAR(8),
  itemComment VARCHAR(30),
  CONSTRAINT item_PK PRIMARY KEY(itemNo),
  CONSTRAINT item_FK FOREIGN KEY(categId) REFERENCES category(categId)
);

Make sure that the categId and sellerId in both tables have the exact same data type. I.e. In the category table, categId should be CHAR(8) and in the feedback table, sellerId should be CHAR(6). — kojow7, Jun 06 '15 at 16:41
To diagnose this problem, remove the FOREIGN KEY constraints from the table definition, and use separate `ALTER TABLE` statements to add the foreign key constraints **`alter table add constraint item_FK2 FOREIGN KEY(sellerId) REFERENCES feedback(sellerId)`**. That way, you can identify which foreign key can't be added. We don't see a table definition for `feedback` table... if that table doesn't exist, we'd expect an error when we try to add a foreign key that references the non-existent table. — spencer7593, Jun 06 '15 at 17:19
The first item table you posted has a foreign key reference to a feedback table. Why is this different than the second item table? Do you have a feedback table? — kojow7, Jun 06 '15 at 17:19

Foreign Key error in MySql

0 Answers0