cannot add foreign key constraint error:

Question

I cannot understand why mysql workbench is showing
cannot add foreign key constraint error: The error is shown at Sales_Order and Purchase_Order table. I cannot figure out what might be the problem. I have created the Er-diagram but cannot run in sql workbench.

/* Dropping the existing table*/

DROP TABLE IF EXISTS Sales_Order; 
DROP TABLE IF EXISTS Purchase_Order; 
DROP TABLE IF EXISTS Supplier;
DROP TABLE IF EXISTS Book; 
DROP TABLE IF EXISTS Customer; 

/* Creating the full sets of Table */

/*Customer Table*/
CREATE TABLE Customer(
Customer_ID INTEGER NOT NULL, 
Customer_Name VARCHAR(255) NOT NULL, 
Customer_Address VARCHAR(255) NOT NULL, 
Customer_Purchase VARCHAR(50) NOT NULL,
Customer_Phone VARCHAR(100) NOT NULL,
CONSTRAINT PKCustomer_ID PRIMARY KEY (Customer_ID)
);

/*Book Table*/
CREATE TABLE Book(
Book_ID  INTEGER NOT NULL,
Book_Name VARCHAR(255) NOT NULL, 
Book_Qty INTEGER NOT NULL, 
Book_Price DECIMAL NOT NULL, 
Book_Author VARCHAR(255) NOT NULL, 
Book_Publisher VARCHAR(255) NOT NULL,
CONSTRAINT PKBook_ID PRIMARY KEY(Book_ID)
);





/*Supplier Table*/
CREATE TABLE Supplier(
Supplier_ID INTEGER NOT NULL,
Supplier_Name VARCHAR(255) NOT NULL, 
Supplier_Address VARCHAR(255) NOT NULL,
CONSTRAINT PKSupplier_ID PRIMARY KEY (Supplier_ID)
); 



/*Sales_Order Table*/
CREATE TABLE Sales_Order(
Sales_ID INTEGER NOT NULL,
Sales_Date INTEGER(20) NOT NULL, 
Sale_Qty INTEGER NOT NULL, 
Discount INTEGER NOT NULL,
CONSTRAINT PKSales_ID PRIMARY KEY (Sales_ID),
CONSTRAINT FKSales_ID FOREIGN KEY (Sales_ID) REFERENCES Customer(Customer_ID),
CONSTRAINT FKSales_ID FOREIGN KEY (Sales_ID) REFERENCES Book(Book_ID)
);

/*Purchase_Table Table*/
CREATE TABLE Purchase_Order(
Purchase_Order_ID INTEGER NOT NULL,
Purchase_Date INTEGER NOT NULL,
Purchase_Description VARCHAR(255) NOT NULL,
Purchase_Qty INTEGER NOT NULL, 
CONSTRAINT PKPurchase_Order_ID PRIMARY KEY (Purchase_Order_ID),
CONSTRAINT FKPurchase_Order_ID FOREIGN KEY(Purchase_Order_ID) REFERENCES Book(Book_ID),
CONSTRAINT FKPurchase_Order_ID FOREIGN KEY(Purchase_Order_ID) REFERENCES Supplier(Book_ID)
);

See here: http://stackoverflow.com/questions/15547276/it-is-possible-to-reference-one-column-as-multiple-foreign-keys It is not possible to have many foreign keys on the same column — Jens, Sep 01 '16 at 08:15
I can't see why it would stop you but the foreign keys don't make a great deal of *sense*. You're claiming, for instance, that `Sales_ID` is not only the primary key of the `Sales_Order` table but that it also identifies both the `Book` and the `Customer`. It would be more normal to have *additional* columns in those tables containing e.g. the `Book_ID` and `Customer_ID`. — Damien_The_Unbeliever, Sep 01 '16 at 08:19
You are using same foreign key name as 'FKSales_ID' where you have to provide unique name to the same table also remove the length provided in 'Sales_Date INTEGER(20)' as you can't specify a column width on data type int. At last this is also duplicate name 'FKPurchase_Order_ID' — Shushil Bohara, Sep 01 '16 at 08:28

score 1 · Answer 1 · answered Sep 01 '16 at 08:30

1

Two foreign keys in the same table cannot have the same name.

Example- FKSales_ID

So ,change the name of one of the foreign key constraint.

answered Sep 01 '16 at 08:30

gunjan maheshwari

306
3
8

score 0 · Answer 2 · answered Sep 01 '16 at 08:34

You are using same foreign key name as 'FKSales_ID' where you have to provide unique name to the same table also remove the length provided in 'Sales_Date INTEGER(20)' as you can't specify a column width on data type int. At last this is also duplicate name 'FKPurchase_Order_ID' and also check last statement where book_id is used in the reference but it's not exist in the Supplier table.

cannot add foreign key constraint error:

2 Answers2