I've tried to look another person's questions but they all seem different from mine.
My problem is I keep getting ERROR 1452 problem "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" even though I think the foreign key is under the constraint.
My tables are
CREATE TABLE `Users` (
`id` int(11) unsigned NOT NULL,
`username` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`joinTime` datetime DEFAULT NULL,
`isRemoved` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username` (`username`),
KEY `isRemoved` (`isRemoved`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `RoleGroups` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`description` text NOT NULL,
`createdTime` datetime NOT NULL,
`lastUpdate` datetime DEFAULT NULL,
`isRemoved` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `isRemoved` (`isRemoved`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
CREATE TABLE `RoleGroupsUsers` (
`groupId` int(11) unsigned NOT NULL,
`userId` int(11) unsigned NOT NULL,
`addedTime` datetime NOT NULL,
KEY `fkGroup_groupId` (`groupId`),
KEY `fkGroup_userId` (`userId`),
CONSTRAINT `fkGroup_groupId` FOREIGN KEY (`groupId`) REFERENCES `RoleGroups` (`id`),
CONSTRAINT `fkGroup_userId` FOREIGN KEY (`userId`) REFERENCES `Users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I hide few attributes from the Users table to simplify the question.
My current data are
mysql> SELECT id, username, joinTime FROM Users ORDER BY joinTime ASC;
+----------+-----------+---------------------+
| id | username | joinTime |
+----------+-----------+---------------------+
| 1 | admin | 2011-07-22 01:20:29 |
| 2 | johndoe | 2011-07-22 01:26:07 |
| 3 | janedoe | 2011-07-22 01:26:07 |
| 31201958 | yonas1 | 2011-07-22 01:32:38 |
| 49494052 | bvnarayan | 2011-07-22 11:51:05 |
+----------+-----------+---------------------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM RoleGroups;
+----+---------------+---------------+---------------------+------------+-----------+
| id | name | description | createdTime | lastUpdate | isRemoved |
+----+---------------+---------------+---------------------+------------+-----------+
| 1 | member | Member | 2011-07-22 01:20:29 | NULL | 0 |
| 2 | administrator | Administrator | 2011-07-22 01:20:29 | NULL | 0 |
+----+---------------+---------------+---------------------+------------+-----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM RoleGroupsUsers;
+---------+----------+---------------------+
| groupId | userId | addedTime |
+---------+----------+---------------------+
| 2 | 1 | 2011-07-22 01:20:29 |
| 1 | 1 | 2011-07-22 01:20:29 |
| 1 | 2 | 2011-07-22 01:26:07 |
| 1 | 3 | 2011-07-22 01:26:07 |
| 1 | 31201958 | 2011-07-22 01:32:38 |
+---------+----------+---------------------+
5 rows in set (0.00 sec)
And when the error popped out when i tried
mysql> INSERT INTO RoleGroupsUsers (groupId, userId, addedTime) VALUES (1, 49494052, '2011-07-22 14:51:05');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`roswell`.`rolegroupsusers`, CONSTRAINT `fkGroup_groupId` FOREIGN KEY (`groupId`) REFERENCES `RoleGroups` (`id`))
It is really strange since there is already a row in RoleGroups table with id 1. And what makes it more strange is this error keeps occurring in one of my workstation while the other running well. My development workstation uses Windows 7 and XAMPP server. And my other workstation uses OS X and MAMP.
UPDATE
It was working few hours ago in the second workstation but I don't know why now it doesn't work. (It might be because I do some configurations in the second workstation that I'm not aware of)
UPDATE AGAIN
The MySQL version
#/Applications/MAMP/Library/bin/mysql --version
/Applications/MAMP/Library/bin/mysql Ver 14.14 Distrib 5.5.9, for osx10.4 (i386) using EditLine wrapper
