Dear friends,

i have a strange problem in mysql while using mutiple tables.I get "Cannot add or update a child row: a foreign key constraint fails" error eventhough i have entered the correct values.if i drop the database and i create a new one,then it will start working.the problem starts only when i restart the machine..if i restart the machine ,the same error will come even thoughi entered the correct values.
hope u understand my problem.

Plz Help,
Thanks in advance

Dear friends,

i fixed the above problem.It occurs because of setting auto_increment value from 1000 for my parent table.If i set auto_increment=1000 for all my child tables,then it is not showing the error.
But still,i have to set auto_increment=1000 for my child tables whenever i restarts the machine.
i dont know what to do.Help me.

Thanks,
Sivakumar

could you post some CREATE TABLE results on your tables so we can see the structure of the tables. Also post your my.cnf if it has some extra settings. Also give some sample INSERTs that make a problem.

Dear friends,
I hope this will be useful to help me.
I set auto_increment=1000 for my EMPLOYEES table.If i set auto_increment=1000 for PROJMEMBER table,it works.But if i restarts the machine,i have to set once again.

mysql> select * from EMPLOYEES;
+------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
| EMPLOYEEID | EMPUSERID | EMPTYPEID | DEPARTMENTID | CURRENTDESIGNATION | OFFICEID | REMARKS | EMPSTATUSID |
+------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
| 1001 | 1 | 1 | 1 | 3 | 1 | | 4 |
| 1002 | 2 | 1 | 1 | 3 | 1 | | 4 |
| 1003 | 3 | 1 | 1 | 3 | 1 | | 4 |
| 1004 | 4 | 1 | 1 | 3 | 1 | | 1 |
| 1005 | 12 | 1 | 1 | 3 | 1 | | 1 |
| 1006 | 13 | 1 | 1 | 3 | 1 | | 1 |
| 1007 | 5 | 1 | 2 | 3 | 1 | | 1 |
| 1008 | 6 | 1 | 2 | 3 | 1 | | 1 |
| 1009 | 13 | 1 | 4 | 3 | 1 | | 1 |
| 1010 | 14 | 1 | 4 | 3 | 1 | | 1 |
| 1011 | 15 | 1 | 4 | 3 | 1 | | 1 |
| 1013 | 8 | 1 | 1 | 3 | 1 | | 1 |
| 1014 | 9 | 1 | 1 | 3 | 1 | | 1 |
+------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
13 rows in set (0.00 sec)

mysql> select * from PROJECT;
+--------+-----------+-----------------------------+---------------+---------------+---------+--------+-------------+
| PROJID | PROJNAME | PROJDESC | STARTDATE | DUEDATE | ESTDAYS | CLIENT | PROJTYPE |
+--------+-----------+-----------------------------+---------------+---------------+---------+--------+-------------+
| 1 | TimeSheet | TimeSheet Management System | 1091557800000 | 1092853800000 | 12 | client | Engineering |
| 2 | HR | HR PROJECT | 1091989800000 | 1092076200000 | 12 | client | Operations |
+--------+-----------+-----------------------------+---------------+---------------+---------+--------+-------------+
2 rows in set (0.00 sec)

mysql> desc PROJMEMBER;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| PROJMEMBERID | int(11) | | PRI | NULL | auto_increment |
| PROJID | int(11) | YES | MUL | NULL | |
| EMPLOYEEID | int(11) | YES | MUL | NULL | |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into PROJMEMBER values(null,1,1003);
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

************************************************** ************************************************** *
alter table PROJMEMBER auto_increment=1000;
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into PROJMEMBER values(null,1,1003);
Query OK, 1 row affected (0.00 sec)

hope I don't bother you much but could you post the results from
SHOW CREATE TABLE EMPLOYEES;
SHOW CREATE TABLE PROJMEMBER;
SHOW CREATE TABLE PROJECT;
Also tell us which version of mysql you're using.

Dear friends,

Iam using mysql 4.0.20-standard version.


SHOW CREATE TABLE EMPLOYEES;

| EMPLOYEES |CREATE TABLE `EMPLOYEES` (
`EMPLOYEEID` int(11) NOT NULL auto_increment,
`EMPUSERID` int(11) NOT NULL default '0',
`EMPTYPEID` int(11) NOT NULL default '0',
`DEPARTMENTID` int(11) NOT NULL default '0',
`CURRENTDESIGNATION` int(11) NOT NULL default '0',
`OFFICEID` int(11) NOT NULL default '0',
`REMARKS` varchar(255) default NULL,
`EMPSTATUSID` int(11) NOT NULL default '0',
PRIMARY KEY (`EMPLOYEEID`),
KEY `EMPTYPES_EMPLOYEES_IND` (`EMPTYPEID`),
KEY `DESIGNATIONS_EMPLOYEES_IND` (`CURRENTDESIGNATION`),
KEY `DEPARTMENTS_EMPLOYEES_IND` (`DEPARTMENTID`),
KEY `EMPUSER_EMPLOYEES_IND` (`EMPUSERID`),
KEY `OFFICE_EMPLOYEES_IND` (`OFFICEID`),
KEY `EMPSTATUS_EMPLOYEES_IND` (`EMPSTATUSID`),
CONSTRAINT `DEPARTMENTS_EMPLOYEES_FK1` FOREIGN KEY (`DEPARTMENTID`) REFERENCES `DEPARTMENTS` (`DEPARTMENTID`),
CONSTRAINT `DESIGNATIONS_EMPLOYEES_FK1` FOREIGN KEY (`CURRENTDESIGNATION`) REFERENCES `DESIGNATIONS` (`DESIGNATIONID`),
CONSTRAINT `EMPSTATUS_EMPLOYEES_FK1` FOREIGN KEY (`EMPSTATUSID`) REFERENCES `EMPSTATUS` (`EMPSTATUSID`),
CONSTRAINT `EMPTYPES_EMPLOYEES_FK1` FOREIGN KEY (`EMPTYPEID`) REFERENCES `EMPTYPES` (`EMPTYPEID`),
CONSTRAINT `EMPUSER_EMPLOYEES_FK1` FOREIGN KEY (`EMPUSERID`) REFERENCES `EMPUSER` (`EMPUSERID`),
CONSTRAINT `OFFICE_EMPLOYEES_FK1` FOREIGN KEY (`OFFICEID`) REFERENCES `OFFICE` (`OFFICEID`)
) TYPE=InnoDB |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.25 sec)

mysql> SHOW CREATE TABLE PROJMEMBER;

| PROJMEMBER | CREATE TABLE `PROJMEMBER` (
`PROJMEMBERID` int(11) NOT NULL auto_increment,
`PROJID` int(11) default NULL,
`EMPLOYEEID` int(11) default NULL,
PRIMARY KEY (`PROJMEMBERID`),
UNIQUE KEY `PROJECTMEMBER` (`PROJID`,`EMPLOYEEID`),
KEY `PROJID_INDEX` (`PROJID`),
KEY `EMPID_INDEX` (`EMPLOYEEID`),
CONSTRAINT `PROJMEM_FK1` FOREIGN KEY (`PROJID`) REFERENCES `PROJECT` (`PROJID`),
CONSTRAINT `PROJMEM_FK2` FOREIGN KEY (`EMPLOYEEID`) REFERENCES `EMPLOYEES` (`EMPLOYEEID`)
) TYPE=InnoDB |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE PROJECT;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table


|
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PROJECT | CREATE TABLE `PROJECT` (
`PROJID` int(11) NOT NULL auto_increment,
`PROJNAME` varchar(100) NOT NULL default '',
`PROJDESC` varchar(255) default NULL,
`STARTDATE` bigint(20) default NULL,
`DUEDATE` bigint(20) default NULL,
`ESTDAYS` bigint(20) default NULL,
`CLIENT` varchar(100) NOT NULL default '',
`PROJTYPE` varchar(20) default NULL,
PRIMARY KEY (`PROJID`)
) TYPE=InnoDB |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

http://www.linuxgazette.com/node/view/9086
I think this might be the solution to your problem. Don't have time to check on it right now. Please, let me know if you made any progress.