issues with foreign key constraint ERROR 1005
Posted by:
Tony Snell
Date: November 01, 2023 08:15AM
I have a large Access DB with many tables & relationships. I have exported the tables to MySQL but it only exports the table structure & data (Not the relationships / restraints, so I now need to recreate all the relationships in MySQL using workbench. I can create the primary keys but when creating foreign keys I get a Duplicate key on write or update error if I am trying have more than one chiled table linked to a parent table no matter what I try.
I have:
Employees table (tbl_Emp_Details) with a primary Key (Emp_ID)
There are many child tables that have a one to many relationship with this table, all linked by the Emp_ID
e.g.
Employee Expenses (tbl_Emp_Expences) with a primary Key (Emp_Expences_ID) & foreign Key (Emp_ID)
or
Employee Alerts (tbl_Emp_Alerts) with a primary Key (Emp_Alerts_ID) & foreign Key (Emp_ID)
I can create the primary key on all tables. I can then create the a foreign key, linked by Emp_ID on one table (e.g. Emp_Expences_ID to Emp_ID of tbl_Emp_Details) but if I try to create a second one to many relationship from tbl_Emp_Details to tbl_Emp_Alerts using Emp_Alerts_ID to Emp_ID I get:
“Duplicate key on write or update”
Full error message:
Operation failed: There was an error while applying the SQL script to the database.
ERROR 1005: Can't create table `TCTSandpit`.`tbl_Emp_Expences` (errno: 121 "Duplicate key on write or update")
SQL Statement:
ALTER TABLE `TCTSandpit`.`tbl_Emp_Expences`
ADD CONSTRAINT `Emp_ID`
FOREIGN KEY (`Emp_ID`)
REFERENCES `TCTSandpit`.`tbl_Emp_Details` (`Emp_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE
Code:
ALTER TABLE `TCTSandpit`.`tbl_Emp_Expences`
ADD CONSTRAINT `Emp_ID`
FOREIGN KEY (`Emp_ID`)
REFERENCES `TCTSandpit`.`tbl_Emp_Details` (`Emp_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE;
I have been working on this for days now and tried everything I can think of, if anyone can help I will be grateful
Subject
Written By
Posted
issues with foreign key constraint ERROR 1005
November 01, 2023 08:15AM
November 03, 2023 09:43AM
November 03, 2023 10:17AM
November 03, 2023 11:37AM
November 03, 2023 10:21AM
November 03, 2023 11:47AM
November 03, 2023 11:52AM
November 03, 2023 10:10PM
November 05, 2023 05:50AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.