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;

[Qulitima]

The error message you're encountering, errno: 121 "Duplicate key on write or update", indicates that there is an issue related to a duplicate constraint name or existing index. This often happens when the MySQL workbench attempts to generate a foreign key constraint with a name that is already used within the database.


MySQL requires that all constraint names be unique within the database. Run the following query to check if the Emp_ID constraint already exists:SELECT CONSTRAINT_NAME, TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'TCTSandpit'
AND CONSTRAINT_NAME = 'Emp_ID';


If you find that a constraint with the same name (Emp_ID) already exists, you will need to use a unique name for the new foreign key constraint.


ou can resolve the issue by giving the foreign key constraint a unique name. For example, instead of naming the constraint Emp_ID, you could call it something like fk_tbl_Emp_Alerts_Emp_ID.


Here's the updated SQL code:ALTER TABLE `TCTSandpit`.`tbl_Emp_Alerts`
ADD CONSTRAINT `fk_tbl_Emp_Alerts_Emp_ID`
FOREIGN KEY (`Emp_ID`)
REFERENCES `TCTSandpit`.`tbl_Emp_Details` (`Emp_ID`)
ON DELETE CASCADE
ON UPDATE CASCADE;