...

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



helloise
06-27-2011, 04:08 PM
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

Fumigator
06-27-2011, 06:50 PM
The error is telling you the data in your tables does not comply with the constraint you're attempting to add.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum