SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist AsianGirl's Avatar
    Join Date
    Oct 2003
    Location
    U.S.A
    Posts
    446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cannot add or update a child row: a foreign key constraint fails

    what does it mean when I got this error ?

    Code:
    Cannot add or update a child row: a foreign key constraint fails
    ?

    the code is in php with mySQL database

  2. #2
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it means exactly what it says..

    If you understand what a foreign key is, which is basically a contraint on a column that says that every value of that column must be in the set contained by the parent column (or null if nulls are allowed)

    So basically, lets say you have two tables, employees and departments..
    now lets say that the employee table contains
    empid, empname, deptno

    empid being the primary key for employee table.

    then deptartment table contains
    deptno, deptname, location

    alright now lets say you make the deptno column within the employee table a foriegn key that references the deptno from the department table.. now, you have established a relationship between the two tables. If you then add some deptartments to the deptartment table, in order to insert the detpartment for each user, that id must exist within the deptartment table (or be null if nulls are allowed -- which might be usefull if a certain employee didnt have a deptartment or the deptartment had not been created yet)

    But now lets say you had department 30 to the department table, and you go and add some employees to the employee table who are from department 30.. if you now try and delete department 30 from the department table, what happens to the employees who are from deptartment 30?? do they get fired!! you need to establish what should happen to those employees.. By default this operation is restricted, and you get the message you saw.. BUt you can change it so that these contraints are cascaded by addding on delete cascade to that constraint.. If you cascade the constraints on a delete, all child entries will be deleted from your database.. In some cases this is what you want, but in most situations you probably don't want to delete all of that information.

    Another option is to add on delete set null, which would make all employees deptartment entries null if they department was deleted.. in some dbms's you can set to a default value on delete as well.
    What you should do is dependant on the business rules though for your database..

    to alter your constraints, use an alter constraint ddl statement

    this might help

    http://dev.mysql.com/doc/refman/5.0/...nstraints.html

    you can also alter/setup constraints in newer versions of phpmyadmin

  3. #3
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, i thought your error said could not delete a parent row.. its the other way around.

    in order to insert an entry into the employee table mentioned above, the deptno for the employee must exist in the department table.. if say you have deptartments 10,20,30,40 in the deptartment table, butyou try and insert an employee with a department 50, you will get the error you saw.. if you try and change an employee with deptartment 30 to deptartment 50, you will get that error, so it may have been either an insert or update. you'll need to check the script to be sure.
    Last edited by mwolfe; Dec 6, 2005 at 20:52.

  4. #4
    SitePoint Evangelist AsianGirl's Avatar
    Join Date
    Oct 2003
    Location
    U.S.A
    Posts
    446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahhh ...thanks for your helpful explanation!


Bookmarks

Posting Permissions

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