April 22, 2012
Mysql alter statement
Question by Eagletrophy
this is my alter statement trying to connect two tables:
ALTER TABLE my_contacts
CHANGE `profession` `profession_id` INT NOT NULL,
ADD CONSTRAINT professions_profession_id_fk
FOREIGN KEY (profession_id) REFERENCES professions (profession_id)
and I have the following error:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`contacts`.<result 2 when explaining filename '#sql-1ca_73'>, CONSTRAINT `professions_profession_id_fk` FOREIGN KEY (`profession_id`) REFERENCES `professions` (`profession_id`))
can anyone guide me on what this is? I am actually learning how to write sql myself since I have used tools all this while.
Answer by verhage
Your foreign key constraint is failing.
The foreign key you are trying to create enforces every profession_id in my_contacts to be present in your professions table. Right now, this is not the case. You should lookup all records in my_contacts where the profession_id is not present in professions and fix those first.
Answer by Starx
This is a relationship conflict. An easier to solve this is
- Remove the relationship between the fields
- Alter the tables
- While adding the relationship again, fix the errors you receive.
