Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'tomato.#sql-2730_1b8' (errno: 121)

Here's the statement it's trying to execute:

ALTER TABLE `tomato`.`ing_allergy_ingredient` 
ADD CONSTRAINT `fk_ai_allergy`
FOREIGN KEY (`allergy_id` )
REFERENCES `tomato`.`ing_allergy` (`allergy_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION

Any ideas what this error means?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

You will get this message if you're trying to add a constraint with a name that's already used somewhere else.

According to this post you can check your constraint like this:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
  constraint_name,
  table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
ORDER BY
  constraint_name;
share|improve this answer
    
The foreign key didn't show up in this query, but when I looked at the table there was an index there with the same name. I believe it's a bug with the synchronization in MySQL Workbench. I downloaded the latest version (5.2.31) and the problem is gone. –  BenV Jan 8 '11 at 22:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.