CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
sanjeev dhakal
sanjeev dhakal

Reputation: 1

cannot add foreign key constraint error:

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

Upvotes: 0

Views: 706

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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.

Upvotes: 0

gunjan maheshwari
gunjan maheshwari

Reputation: 306

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.

Upvotes: 1

Related Questions