Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: alter table foreign key error

    i was trying add foreign key to existing table n with a reference to other table which is a primary key in the other table i don't understand why error is coming

    Code:
    ALTER TABLE pp_invoice add CONSTRAINT fk_porderno FOREIGN KEY (pp_invoice_porderno) REFERENCES pp_purchaseorder(pp_po_no)
    error
    "Cannot add or update a child row: a foreign key constraint fails (`ppmcsdatabase`.<result 2 when explaining filename '#sql-a48_1809'>, CONSTRAINT `fk_porderno` FOREIGN KEY (`pp_invoice_porderno`) REFERENCES `pp_purchaseorder` (`pp_po_no`))"

    plzzzz help meee
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so fix your data
    its telling you that it cannot apply to foreign key constraint because there is pre existing data which which stops the constraint being applied.

    so you need to find what row(s) in pp_invoice either have no purchase order defined, or have a purchase order defiened but one that doesn't exist pp_purchaseorder
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    so i had to clear the data from both tables right healdem ??
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    you have to make certain that before you apply the foreign key constraint that each column in the 'child' table on that proposed key has a matching column in the 'parent' table. granted if you cannot easily find the value in the child table that causes the foreign key constraint to fail then it may be easier for you to delete all rows. however you shouldn't need to do so.

    its easier to define the relatinships prior to putting data in, you just make it harder trying to do it afterwards, and it nearly always makes me think that not enough thought went into the initial design

    so it could be that you are missing a value present in the child table but not in the parent table

    essentially you have to go through your data and work out what is wrong. having done so you need to establish if the problem is a simple data capture problem or indicative of a design flaw.. ferinstance should the column be NULLable.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by healdem View Post
    no
    you have to make certain that before you apply the foreign key constraint that each column in the 'child' table on that proposed key has a matching column in the 'parent' table. granted if you cannot easily find the value in the child table that causes the foreign key constraint to fail then it may be easier for you to delete all rows. however you shouldn't need to do so.

    its easier to define the relatinships prior to putting data in, you just make it harder trying to do it afterwards, and it nearly always makes me think that not enough thought went into the initial design

    so it could be that you are missing a value present in the child table but not in the parent table

    essentially you have to go through your data and work out what is wrong. having done so you need to establish if the problem is a simple data capture problem or indicative of a design flaw.. ferinstance should the column be NULLable.
    thanks a lot i got the point wt u say n worked on it got the result tooo how can i say thanks really thanks a lot lot lot n a big cheers to uuuu healdem
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

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