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

[helloise]

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_type_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

[gizmola]

My best guess is that in rc_sent_items_table, there is a row which has a profile_id_to, that has no corresponding match to a rc_profile_table.id value.