MySQL : add constraint gives:ERROR 1452 (23000): Cannot add or update a child row?

i have the following:

mysql> show create table rc_profile_table \G;
*************************** 1. row ***************************
Table: rc_profile_table
Create Table: CREATE TABLE `rc_profile_table` (
`id` int(11) NOT NULL auto_increment,
`created_at` datetime default NULL,
`name` varchar(32) NOT NULL,
`surname` varchar(32) NOT NULL,
`password` varchar(128) NOT NULL,
`unique_code` varchar(16) NOT NULL,
`msisdn` varchar(32) NOT NULL,
`current_location` varchar(64) NOT NULL,
`profile_pic` varchar(255) default NULL,
`email` varchar(128) NOT NULL,
`age` int(11) NOT NULL,
`city_of_birth` varchar(64) NOT NULL,
`personality` varchar(128) NOT NULL,
`country_id` int(11) NOT NULL,
`religious_id` int(11) NOT NULL,
`relationship_id` int(11) NOT NULL,
`wants_and_needs` varchar(512) default NULL,
`hopes_and_aspirations` varchar(512) default NULL,
`profession` varchar(128) default NULL,
`hobbies_and_interests` varchar(512) default NULL,
`skills_and_talents` varchar(512) default NULL,
`open_comment` varchar(512) default NULL,
`food_and_drinks` varchar(512) default NULL,
`activated` int(11) default NULL,
`mood_updated_at` datetime default NULL,
`mood_color` varchar(32) default NULL,
`mood_desc` varchar(64) default NULL,
`login_count` int(10) unsigned default '0',
`campus_id` int(11) default NULL,
PRIMARY KEY  (`id`),
KEY `rc_profile_table_FI_2` (`country_id`),
KEY `rc_profile_table_FI_3` (`religious_id`),
KEY `rc_profile_table_FI_4` (`relationship_id`),
KEY `rc_profile_table_FI_5` (`campus_id`),
CONSTRAINT `rc_profile_table_FK_2` FOREIGN KEY (`country_id`) REFERENCES `rc_country_table` (`id`) ON DELETE CASCADE,
CONSTRAINT `rc_profile_table_FK_3` FOREIGN KEY (`religious_id`) REFERENCES `rc_religious_type_table` (`id`) ON DELETE CASCADE,
CONSTRAINT `rc_profile_table_FK_4` FOREIGN KEY (`relationship_id`) REFERENCES `rc_relationship_type_table` (`id`) ON DELETE CASCADE,
CONSTRAINT `rc_profile_table_FK_5` FOREIGN KEY (`campus_id`) REFERENCES `rc_campus_table` (`id`) ON DELETE CASCADE
)   
ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

and also:

mysql> show create table rc_sent_items_table \G;
*************************** 1. row ***************************
Table: rc_sent_items_table
Create Table: CREATE TABLE `rc_sent_items_table` (
`profile_id_from` int(11) NOT NULL,
`profile_id_to` int(11) NOT NULL,
`message` varchar(512) default NULL,
`subject` varchar(255) default NULL,
`opened_once` int(11) default NULL,
`message_type_id` int(11) default NULL,
`id` int(11) NOT NULL auto_increment,
`created_at` datetime default NULL,
PRIMARY KEY  (`id`),
KEY `rc_sent_items_table_FI_1` (`profile_id_from`),
KEY `rc_sent_items_table_FI_2` (`profile_id_to`),
KEY `rc_sent_items_table_FI_3` (`message_type_id`),
CONSTRAINT `rc_sent_items_table_FK_3` FOREIGN KEY (`message_t开发者_开发百科ype_id`) REFERENCES `rc_message_type_table` (`id`) ON DELETE CASCADE
)  
ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

so when i try to do:

ALTER TABLE rc_sent_items_table ADD CONSTRAINT `rc_sent_items_table_FK_1`
FOREIGN  KEY (`profile_id_to`) REFERENCES `rc_profile_table` (`id`) ON DELETE CASCADE;

i get this error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`traffic2/#sql-1122_5cf`, CONSTRAINT `rc_sent_items_table_FK_1` FOREIGN KEY
(`profile_id_to`) REFERENCES `rc_profile_table` (`id`) ON DELETE CASCADE)

What am I doing wrong please? thank you

---

What went wrong?

You asked mysql to enforce a relationship between the two tables which it was previously unaware of. Unfortunately the state of the two tables indicates that the relationship is already broken. That's just bad luck. Now you have some records in the rc_sent_items_table with profile_ids that don't exist in the rc_profile_table.

What do I do now

You just need to decide if the records in the rc_sent_items_table are valid or if you should just delete them. That's a call that only you can make. It could well be that you need them and will then have to add corresponding records to the rc_profile_table.

Which records do I need to fix?

You can find which records cannot have the constraint applied i.e. which you need to fix by running something like this ...

select * from rc_sent_items_table 
where profile_id not in (select profile_id from rc_profile_table)

That will cross-check the message_type_id in the 'broken' table with the table it references.

Using innodb status

You may also get some useful pointers by checking what innodb thinks. If you use the '\G' flag when running the command you should get something like the sample below with some warnings listed early on.

mysql> show innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
110627 16:06:50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 4, OS waits 3
------------
TRANSACTIONS
------------
Trx id counter 0 167168
Purge done for trx's n:o < 0 166758 undo n:o < 0 0
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 996, OS thread id 3013684080
MySQL thread id 34, query id 99 localhost root
show innodb status

---

You are trying to add new FK to rc_sent_items_table; but what if this table contains some data? Check that field rc_sent_items_table.profile_id_to has appropriate values in accordance with rc_profile_table.id.