Problems creating a Foreign Key using MySQL workbench

[Ant_Snell]

I have an Access DB that needs splitting and backend hosted on a VPN as MySQL tables. I had not previously used MySQL but have successfully exported all the tables to MySQL then linked back to Access using a ODBC connector. I originally thought that the Access Export tool would create all the relationships and all I would need to do is then link to them. However this was not the case, I have to first create all the Primary & Foreign Keys. Using the MySQL workbench I have successfully created Primary Keys and also the lookup table relationships for combo lists however when trying to create the Foreign keys for the 1 -Many relationships I keep getting errors. I have 50+ that need to be done but the below is an example of just one along with the MySQL script.

Tbl_Emp_Details		Tbl_Emp_Alert
PK Emp_ID		PK Emp_Alert_ID
Emp_First_Name		FK Emp_ID
Emp_Last_Name

Trying to create a one to many between PK Emp_ID & FK Emp_ID

Code

1. ALTER TABLE `TCTSandpit`.`tbl_Emp_Alerts`
2. ADD CONSTRAINT `Emp_ID`
3. FOREIGN KEY (`Emp_ID`)
4. REFERENCES `TCTSandpit`.`tbl_Emp_Details` (`Emp_ID`)
5. ON DELETE CASCADE
6. ON UPDATE CASCADE;

ERROR 1005: Can't create table `TCTSandpit`.`tbl_Emp_Alerts` (errno: 121 "Duplicate key on write or update")



The code was created using the MySQL workbench GUI. Any help on how to create the relationship appreciated.

[davegri]

If the constraint forbids duplicate FK values, that's the problem.

[Ant_Snell]

Thanks Davegri,
How can I check if the constraint does forbid duplicate keys? I saw the reference to duplicates in the error massage but don’t understand what it is telling me. The child table only contains one foreign key (Emp_ID) related to Emp_ID in the parent table. The data will only contain one value of a type e.g. if the employee has an Alert (Allergy, Phobia etc.) they may have one of each but never the same one twice. Emp_ID is used as a foreign key in other tables e.g. addresses, contact numbers, etc. but surly I don’t need to give it a different name in each table? The one to many relationship works fine in Access but MySQL will not allow me to create it in the workbench.

[davegri]

Thanks Davegri,
How can I check if the constraint does forbid duplicate keys? I saw the reference to duplicates in the error massage but don’t understand what it is telling me. The child table only contains one foreign key (Emp_ID) related to Emp_ID in the parent table. The data will only contain one value of a type e.g. if the employee has an Alert (Allergy, Phobia etc.) they may have one of each but never the same one twice. Emp_ID is used as a foreign key in other tables e.g. addresses, contact numbers, etc. but surly I don’t need to give it a different name in each table? The one to many relationship works fine in Access but MySQL will not allow me to create it in the workbench.

Don't know anything about workbench. Workbench documentation should explain what the parameters are for. Maybe just drop the ADD CONSTRAINT line?

[Ant_Snell]

1. ALTER TABLE `TCTSandpit`.`tbl_Emp_Alerts`
2. ADD CONSTRAINT `Emp_ID`
3. FOREIGN KEY (`Emp_ID`)
4. REFERENCES `TCTSandpit`.`tbl_Emp_Details` (`Emp_ID`)
5. ON DELETE CASCADE
6. ON UPDATE CASCADE;