none
Update foreign key in detail table after updating parent table not working

    Question

  • Hi everybody,
    new problem for your big brains!
    I am stuck at a problem while trying to insert rows into two tables bound with a foreign key constraint using a typed DataSet.  The insert into the first table (the ‘parent’ table) works fine, but the attempt to insert a row into the dependant table fails.
    I use VS2008, C# and SQL CE Database.

    I saw this code but it is not working.

     

    DataRow parentRow = parentTable.NewRow();
    
    // populate parentRow's columns here
    
    parentTable.Rows.Add(parentRow);
    
    DataRow childRow = childTable.NewRow();
    
    childRow["ParentID"] = parentRow["ID"];  //<--- THIS SEEMS TO ME TO BE WRONG BECAUSE IT DOESN'T CREATE THE RELATIONSHIP
    
    // populate remainder of childRow's columns here
    
    childTable.Rows.Add(childRow);
    Instead of
    childRow["ParentID"] = parentRow["ID"];

     

    I would write
    childRow.SetParentRow(parentRow);
    after setting the DataRelation's ForeignKeyConstraint UpdateRule to Rule.Cascade .

    But, it doesn't work. I still have all the foreign key in the detail table set to -1, -2 and so on.

    I saw this also, but I don't want to use either StoredProcedure or @@IDENTITY/SCOPE_IDENTITY call. Just ADO.NET classes.

    Can Anyone help me?
    ------- Life is what happens while doing other projects -------
    Tuesday, September 08, 2009 9:10 PM

Answers

  • I found the solution here.

    http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/0408b9be-16fa-4d93-bf2b-2d749e563eb1

    I don't like it because it's not as clean as I would, but it is the only one available.

    Thx to everybody.

    ------- Life is what happens while doing other projects -------
    • Marked as answer by Italian Cousin Thursday, September 10, 2009 12:41 PM
    Thursday, September 10, 2009 11:40 AM

All replies

  • Did you specify that the ID of the parent is a primary key of the table?  Did you set up a relationship in the dataset that associated the two tables?  If you did all that then ADO.NET will manage the relationship.  Generally you will set the primary key to use a seed of -1 and a step of -1 so that it counts backwards from 0.  This makes it easy to identify new rows and avoids accidentally associating a new child with an existing row.  When ADO.NET pushes the data to the DB the DB will generate the real primary key value but the relationship will still be set up properly.  I can't remember off the top of my head whether you need to explicitly query for the new primary key or whether ADO.NET updates the dataset but that should be easy to verify.

    Here's some helper links:
    http://msdn.microsoft.com/en-us/library/system.data.datarelation.aspx
    http://msdn.microsoft.com/en-us/library/ay82azad.aspx
    http://a2zdotnet.com/View.aspx?id=97


    Michael Taylor - 9/9/09
    http://p3net.mvps.org
    Wednesday, September 09, 2009 2:34 PM
  • This is my code in detail:

    //Populating new parent row
    MyDataset.parentTableRow parentRow = MyDataset.parentTable.NewparentTableRow();
    parentRow.Field1 = drRow["Field1"].ToString();
    parentRow.Field2 = drRow["Field2"].ToString();
    parentRow.Field3 = drRow["Field3"].ToString();
    parentRow.Field4 = Convert.ToInt16(drRow["Field4"]);
    parentRow.Field5 = drRow["Field5"].ToString();
    parentRow.Field6 = drRow["Field6"].ToString();
    parentRow.Field7 = drRow["Field7"].ToString();
    //...
    MyDataset.parentTable.AddparentTableRow(parentRow);
    
    //Populating a detail row and setting the relation
    MyDataset.detailRow imagerow = MyDataset.detailTable.NewdetailTableRow();
    //BREAKPOINT 1
    detailRow.parentTableRow = parentRow;
    //BREAKPOINT 2
    detailRow.Field1 = 0;
    detailRow.Field2 = 0;
    //...
    MyDataset.detailTable.AdddetailTableRow(detailRow);
    
    //ParentTable and Detail Table are linked by a Relation And Foreign Key Constraint with Update Rule = Cascade which ties parent table primary key and detail table foreign key.
    
    parentTableTableAdapter.Update(MyDataset);
    //BREAKPOINT 3
    detailTableTableAdapter.Update(MyDataset);
    //BREAKPOINT 4
    parentTableTableAdapter.Fill(MyDataset.parentTable);
    //BREAKPOINT 5
    detailTableTableAdapter.Fill(MyDataset.detailTable);

    At runtime, I get the following:
    at bp1 detail table foreign key is null;
    at bp2 foreign key gets equal to parent table primary key (-1);
    at bp3 all primary key in parent table are numbered -1, -2, -3 and so on;
    at bp4 all foreign key in detail table are numbered -1, -2, -3 and so on;
    at bp5 all primary keys in parent table are numbered 1,2,3 and so on;
    next line raise an exception: "[System.Data.ConstraintException] = {"It is impossible to activate constraints. One or more rows contain values violating not-null, unique or foreign key constraint."}

    and I don't know how to proceed...



    ------- Life is what happens while doing other projects -------
    Wednesday, September 09, 2009 5:47 PM
  • In your relation did you set it to be both ways?  By default the relation is enforced from the server to the client but not the other way around.  When the first update runs it updates the table with the new PKs but the child table doesn't get those changes.  Set the relation to Both Relation and FK and after the first update completes it should update the child table such that its update method will succeed.

    Michael Taylor - 9/9/09
    http://p3net.mvps.org

    Wednesday, September 09, 2009 6:29 PM
  • I created a constraint between P.T. primary key and D.T. foreign key.
    This link is a relation and foreign key constraint with update rule set to Cascade.

    No other properties set. Is this both way?
    Is it sufficient? Seems it is not because it's working as I explained before.

    May I use a TableAdapterManager to better support hierarchical updates?

    ------- Life is what happens while doing other projects -------
    Wednesday, September 09, 2009 6:52 PM
  • No it isn't both ways.  If you are using the DS designer then right-click the relationship line and select Edit Relation.  Then check the option that says both ways.

    I don't know anything about TableAdapterManager.


    Michael Taylor - 9/9/09
    http://p3net.mvps.org
    Wednesday, September 09, 2009 8:10 PM
  • Relationship selected and Edit Relation clicked.
    There's no option like the one you're talking about.

    ------- Life is what happens while doing other projects -------
    Wednesday, September 09, 2009 8:45 PM
  • In the relationship dialog that should appear select the option Both Relation and Foreign Key Constraint under Choose what to create.  The default is relation only.  That means that the dataset is created with a DataRelation that maps the child to the parent.  If you select the FK constraint option then it creates a ForeignKeyConstraint on the child to ensure it matches a parent row.  If you use both then it creates both the relation and the constraint.  The relation hooks up the child to the parent when retrieving from the DB.  The constraint ensures that the child always contains a valid reference to a parent and allows/enforces the update requirement.

    If it doesn't appear then please confirm the version of VS you are running and what DB you are connecting to.

    Michael Taylor - 9/9/09
    http://p3net.mvps.org
    Wednesday, September 09, 2009 9:04 PM
  • OK. So you mean "Both Relation and Foreign Key Constraint" not "Both ways"
    Well, it is already like this!!!!
    But it doesn't update detail foreign key!

    Now, please, don't say you give up!

    ------- Life is what happens while doing other projects -------
    Wednesday, September 09, 2009 9:09 PM
  • Make sure that for your relation you have set Update and Delete rules to Cascade.  You should leave AcceptRejectRule as None.

    Michael Taylor - 9/9/09
    http://p3net.mvps.org

    Wednesday, September 09, 2009 9:31 PM
  • Hi all,

    Let me have a guess, (I don't have the access to a SQL server right to prove it today but will do when I get a chance).

     
    There are two things here:

    1. The PK column is setup with AutoIncreament = true and Seed and Step = -1, yes, this is the default setup by dataset designer to avoid the concurrency issue. 

     

    1. You do not refresh the PK when you update the parent table. As you said  

    "I don't want to use either StoredProcedure or @@IDENTITY/SCOPE_IDENTITY call"

    And for SQL CE, we do not have the option for you to Auto Refresh the PK.  If you are using SQL server, then yes. The auto generated SQL command will have an option to refresh the PK after insert.

     

     

    Here is what is happening in your case:

     

    1  let's say you have added these  two rows

           ParentRow(PK=-1, "P1")

           Child Row(PK=-1, FK=-1, "C1")

           Child Row(PK=-2, FK=-1, "C1")

           Child Row(PK=-3, FK=-1, "C1")

      

    They are related by FK=-1

     

    2 Now you call ParentAdapter.Update(ParentTable)

    In the database, this will become, e.g.,

    ParentRow (PK=1, "P1")  (assume the database is empty)


    3 Then you call ChildAdapter.Update(ChildTable)

    In the database, this will become

    Child Row(PK=1, FK=-1, "C1")

    Child Row(PK=2, FK=-1, "C1")

    Child Row(PK=3, FK=-1, "C1")

    Notice that the FK is still -1, (you can open the database and check it yourself.

     

    4 If you do ParentAdapter.Fill, it will fill up

    ParentRow (PK=1, "P1") 


    5 Then If you do ChildAdapter.Fill, it will try to fill up

    Child Row(PK=1, FK=-1, "C1")

    Child Row(PK=2, FK=-1, "C1")

    Child Row(PK=3, FK=-1, "C1")

    Because FK=-1 does not exist anymore, you fail!

            

     

    Now if you do refresh the PK during update, it will happen this way:

    2  you call ParentAdapter.Update(ParentTable)

    In the database, this will become, e.g.,

    ParentRow (PK=1, "P1")  (assume the database is empty)

    2.b And in the DataSet the ParentRow is change to PK=1 as well.

    2.c. Because you set the constraint with update rule = cascade, the child row will be updated as well

    Child Row(PK=-1, FK=1, "C1")

    Child Row(PK=-2, FK=1, "C1")

    Child Row(PK=-3, FK=1, "C1")

     Notice it is FK=1 now.


    3 Then you call ChildAdapter.Update(ChildTable)

    In the database, this will become

    Child Row(PK=1, FK=1, "C1")

    Child Row(PK=2, FK=1, "C1")

    Child Row(PK=3, FK=1, "C1")

     

    4 If you do ParentAdapter.Fill, it will fill up

    ParentRow (PK=1, "P1") 


    5 Then If you do ChildAdapter.Fill, it will also fill up

    Child Row(PK=1, FK=1, "C1")

    Child Row(PK=2, FK=1, "C1")

    Child Row(PK=3, FK=1, "C1")


     

    One quick fix maybe to set AutoIncreament Seed and Step = 1, but I am not sure what could be the bad effect of this. I think this maybe appropriate for local database like SQL CE.


    I think there should be other solutions. 
     


    John Chen -- See my team blog: http://blogs.msdn.com/vsdata. All my posts are provided "AS IS" with no warranties, and confer no rights.
    Thursday, September 10, 2009 4:02 AM
  • TableAdapter is used to help you automatically sort the update order: e.g. Insert parent row first, and then child row. 
    It can be detail to the row level. e.g. If you have self-referenced table. You will have trouble to do it yourself without the help of TableAdapterManager.

    However, for this issue, TableAdapterManager won't help.

    John Chen -- See my team blog: http://blogs.msdn.com/vsdata. All my posts are provided "AS IS" with no warranties, and confer no rights.
    Thursday, September 10, 2009 4:05 AM
  • OK, you seem to have caught the point.
    Because of this issue's hardness, I can also accept solutions with use of @@IDENTITY/SCOPE_IDENTITY parameter.
    But obviously not stored procedure, because I'm using SQL CE and I don't have them!

    What you described in step 2c, that Foreign Key becomes positive, doesn't happen.
    That would surely be the best behavior. Why this?
    How can I get it done?

    ------- Life is what happens while doing other projects -------
    Thursday, September 10, 2009 11:20 AM
  • I found the solution here.

    http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/0408b9be-16fa-4d93-bf2b-2d749e563eb1

    I don't like it because it's not as clean as I would, but it is the only one available.

    Thx to everybody.

    ------- Life is what happens while doing other projects -------
    • Marked as answer by Italian Cousin Thursday, September 10, 2009 12:41 PM
    Thursday, September 10, 2009 11:40 AM