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