none
Question about DataSet/DataRelations and inserting new records RRS feed

  • Question

  • I have a table T3 which has foreign key relations with T1 and T2.
    The columns are as follows:

    T1.C1

    T2.C2

    and

    T3.C1  <-> T1.C1  --fk relation with T1
    T3.C2  <-> T2.C2  --fk relation with T2
    T3.C3  -- T3 specific Columns
    ...


    So, I used the dataset designer in VS2005 to create a ds (dsT3), and I defined the relationships in the designer as described. I used Cascade for all of the options.

    I was "hoping" that if I attempted to INSERT a new record using dsT3 that somewhere magically, the relations would take care of inserting T1.C1 and T2.C2 if they didn't exist. Basically, I filled the dataset, and then called Update.

    I found out that I do in fact get an integrity constraint violation if said columns aren't inserted first.


    I'm confused about the purpose of the DataRelation object if it can't be used for enforcing data integrity - maybe it can, I just don't understand how.

    I'm sure this is a common workflow, but I'm new to ADO.NET.

    What is the best way to ensure integrity on Parent tables using a Dataset such as mine above? I realize I could write SQL to check first and INSERT if required, but "yuck".

    Thanks for any suggestions, and or Links to helpful articles.

    -HS

    Wednesday, February 6, 2008 11:35 PM

Answers

  • The DataRelation enforces relational integrity by throwing an exception if you violate it.  So if you insert a child row

     

    Cascading doesn't work the way you think it does.  Updates cascade down from the parent to the child; if you change the parent's row's primary key, the cascading update will make the same change to the foreign key in the child row.

     

    DataRelations won't magically add a parent row if you add a child row whose FK doesn't exist in the parent table.  It would only be possible for them to do so if the parent table had no columns defined with AllowNulls = False and DefaultValue not set, and that's really a special case, not the general case.

     

    If you've filled your DataSet from the database, you don't have to write SQL to do anything:  you check the parent DataTable to see if inserting a new row is required, and you create a new DataRow (populating any nullable non-defaulted columns) if it is.  The DataAdapter's Update method inserts new parent rows before it inserts new child rows, updating them with their database-assigned PK values (assuming that these are autonumber/identity columns) - which then cascade down to the child rows before you insert them.

    • Marked as answer by Hedley Sohn Monday, March 9, 2009 9:25 PM
    Thursday, February 7, 2008 10:37 AM