Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Angry Unanswered: foreign key checks

    I have 3 tables EMPLOYEES,PROJECT,PROJMEMBER.if i insert values in to PROJMEMBER ,it gives error as
    "ERROR 1216: Cannot add or update a child row: a foreign key constraint fails" eventhough i entered the right values.This problem do not come when i create the tables.Once i restarted the machine,This error comes.

    My create table status :

    mysql> show create table PROJECT;




    |

    | 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(25) default NULL,
    PRIMARY KEY (`PROJID`)
    ) TYPE=InnoDB |



    mysql> 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.00 sec)

    mysql> show create table PROJMEMBER;

    | Table | Create Table


    |

    | PROJMEMBER | CREATE TABLE `PROJMEMBER` (
    `PROJMEMBERID` int(11) NOT NULL auto_increment,
    `PROJID` int(11) NOT NULL default '0',
    `EMPLOYEEID` int(11) NOT NULL default '0',
    PRIMARY KEY (`PROJMEMBERID`),
    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 |





    mysql> select * from PROJECT;
    +--------+-----------+----------+---------------+---------------+---------+--------+-------------+
    | PROJID | PROJNAME | PROJDESC | STARTDATE | DUEDATE | ESTDAYS | CLIENT | PROJTYPE |
    +--------+-----------+----------+---------------+---------------+---------+--------+-------------+
    | 1 | TimeSheet | TMS | 1093804200000 | 1093890600000 | 12 | Dhyan | Engineering |
    +--------+-----------+----------+---------------+---------------+---------+--------+-------------+
    1 row in set (0.00 sec)

    mysql> select * from EMPLOYEES;
    +------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
    | EMPLOYEEID | EMPUSERID | EMPTYPEID | DEPARTMENTID | CURRENTDESIGNATION | OFFICEID | REMARKS | EMPSTATUSID |
    +------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
    | 1 | 1 | 1 | 1 | 3 | 1 | | 4 |
    | 2 | 2 | 1 | 1 | 3 | 1 | | 4 |
    | 3 | 3 | 1 | 1 | 3 | 1 | | 4 |
    | 4 | 4 | 1 | 1 | 3 | 1 | | 1 |
    | 5 | 5 | 1 | 1 | 3 | 1 | | 1 |
    | 6 | 6 | 1 | 1 | 3 | 1 | | 1 |
    | 7 | 7 | 1 | 1 | 3 | 1 | | 1 |
    | 8 | 8 | 1 | 1 | 3 | 1 | | 1 |
    | 9 | 9 | 1 | 1 | 3 | 1 | | 1 |
    | 10 | 10 | 1 | 1 | 3 | 1 | | 1 |
    | 11 | 11 | 1 | 1 | 3 | 1 | | 1 |
    | 12 | 12 | 1 | 1 | 3 | 1 | | 1 |
    | 13 | 13 | 1 | 1 | 3 | 1 | | 1 |
    | 14 | 14 | 1 | 2 | 3 | 1 | | 1 |
    | 15 | 15 | 1 | 2 | 3 | 1 | | 1 |
    | 16 | 16 | 1 | 4 | 3 | 1 | | 1 |
    | 17 | 17 | 1 | 4 | 3 | 1 | | 1 |
    +------------+-----------+-----------+--------------+--------------------+----------+---------+-------------+
    17 rows in set (0.00 sec)

    mysql> select * from PROJMEMBER;
    Empty set (0.00 sec)

    mysql> INSERT INTO PROJMEMBER VALUES(null,1,1);
    ERROR 1216: Cannot add or update a child row: a foreign key constraint fails
    mysql>
    The above is my error.
    Help Me to solve this.

  2. #2
    Join Date
    Aug 2004
    Posts
    3
    I just tried testing your schema and it worked.

    At the top of the query to perform the insert into PROJMEMEBER I added:
    SET_FOREIGN_KEY_CHECKS=0;

    Let me know how you do -

    Noga

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    Your projmemberid is set to not null, yet when you are trying to insert your values you are attempting to set this value to null. Could this be your problem?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •