none
MySQL Parent/Child Relationship RRS feed

  • Question

  • Hi there! Here are my tables:

    CREATE TABLE `parent` (  
      `parentID` int(11) NOT NULL AUTO_INCREMENT,  
      `namevarchar(45) NOT NULL,  
      PRIMARY KEY (`parentID`)  
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1  
     
    CREATE TABLE `child` (  
      `childID` int(11) NOT NULL AUTO_INCREMENT,  
      `parentID` int(11) NOT NULL,  
      `type` varchar(45) NOT NULL,  
      PRIMARY KEY (`childID`),  
      KEY `FK_child_1` (`parentID`),  
      CONSTRAINT `FK_child_1` FOREIGN KEY (`parentID`) REFERENCES `parent` (`parentID`) ON DELETE CASCADE ON UPDATE CASCADE 
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 

    Here is my code:
    1 using System;  
    2 using System.Collections.Generic;  
    3 using System.Text;  
    4 using MySQL_Test.DataSet1TableAdapters;  
    5  
    6 namespace MySQL_Test  
    7 {  
    8     class Program  
    9     {  
    10         static void Main(string[] args)  
    11         {  
    12             parentTableAdapter pta = new parentTableAdapter();  
    13             childTableAdapter cta = new childTableAdapter();  
    14             DataSet1.parentDataTable pdt = new DataSet1.parentDataTable();  
    15             DataSet1.childDataTable cdt = new DataSet1.childDataTable();  
    16             DataSet1.parentRow pr;  
    17             DataSet1.childRow cr;  
    18  
    19             pr = pdt.NewparentRow();  
    20             pr.name = "fruit";  
    21             pdt.AddparentRow(pr);  
    22             cr = cdt.NewchildRow();  
    23             cr.parentID = pr.parentID;  
    24             cr.type = "banana";  
    25             cdt.AddchildRow(cr);  
    26             cr = cdt.NewchildRow();  
    27             cr.parentID = pr.parentID;  
    28             cr.type = "orange";  
    29             cdt.AddchildRow(cr);  
    30             pta.Update(pdt);  
    31             cta.Update(cdt);  
    32         }  
    33     }  
    34

    My dataset was created by dragging the tables from Server Explorer. I manually added to the FK the cascade on update/delete.
    Also both PKs are AutoIncrement with seed -1 and increment -1.

    It runs until line 31, when I receive this message:

    MySql.Data.MySqlClient.MySqlException was unhandled
      Message="Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `FK_child_1` FOREIGN KEY (`parentID`) REFERENCES `parent` (`parentID`) ON DELETE CASCADE ON UPDATE CASCADE)"
      Source="System.Data"
      ErrorCode=-2147467259
      Number=1452
      StackTrace:
           at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
           at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
           at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
           at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
           at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
           at MySQL_Test.DataSet1TableAdapters.childTableAdapter.Update(childDataTable dataTable) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\MySQL Test\MySQL Test\DataSet1.Designer.cs:line 1224
           at MySQL_Test.Program.Main(String[] args) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\MySQL Test\MySQL Test\Program.cs:line 31
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()


    Any information about how to resolve this issue would be very appreciated! Thanks!
    The noodle!
    • Edited by CrackerJack Saturday, February 14, 2009 6:25 AM Added autoincrement info.
    Saturday, February 14, 2009 6:22 AM

Answers