none
Best practice for using FK constraints and cascading? RRS feed

  • Question

  • In SQL Server 2008 I have defined parent and child tables with a FK constraint where the child table has the parent key in it. I also have cascading updates and deletes enabled. Using a typed dataset in VS2008, I have turned on "Both Relation and FK Constraint" and set the update and delete rules to cascading.

    In my hierachial grid, I want to rename the parent master key and have the child's foreign key also be renamed and that works. Problem comes when I update the dataset back to the SQL Server I get a concurrency issue for the [now obvious] reason that the parent record is updated first and then the child record is updated which throws the exeption since the server cascading update probbly already renamed the child foreign key.

    What is the best practice here? I have tried various techniques but found nothing really satisfactory yet. Ideally I would like to keep the constraints in the database but absolutley need to allow a parent key rename reflect in the child in the datagrid.

    Any suggestions appreciated. Thanks, Dave
    Sunday, May 31, 2009 2:54 PM

All replies

  • So to confirm, you have added the appropriate constraints?

    //Example
    ForeignKeyConstraint fk = new ForeignKeyConstraint("fk", ds.Tables[0].Columns["custid"],
            ds.Tables[1].Columns["custid"]);
    fk.DeleteRule = Rule.Cascade;
    fk.UpdateRule = Rule.Cascade;
    ds.Tables[1].Constraints.Add(fk);
    ds.EnforceConstraints = true;

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, May 31, 2009 5:52 PM
  • Yes but not in that manner. Also note I am using names as the keys and not an id. The parent/child relationship and cascading are defined in the database and I used the VS2008 typed dataset wizard to generated dataset adn dataadapter code which imported the relationship and offered to allow me to also set the cascading THe code is effectively the same code as yours which is shown below. I'm thinking because I have the FK relationship enforced in the database, and the cascading defined in the database as well, that is why I can't update both tables from ADO.NET. Seems like a catch-22 since I need the FK/cascading behaviour on the client in order to visually see the child FK change when I rename the parent key. But then when the parent row is updated to the database, I think the child FK changes due to the cascading and then when the child row is updated it fails. Both table updates are inside of a transaction. Also, the generated code is adding the constraint to the parent table, not the child, and I can't tell form your example which is being updated, parent or child. - Thanks, Dave

     

    global::System.Data.ForeignKeyConstraint fkc;

    fkc =
    new global::System.Data.ForeignKeyConstraint("tb_TaskDefinitions_tb_Tasks", new global::System.Data.DataColumn[] {this.tabletb_TaskDefinitions.DefNameColumn}, new global::System.Data.DataColumn[] {this.tabletb_Tasks.DefNameColumn});this.tabletb_Tasks.Constraints.Add(fkc);

    fkc.AcceptRejectRule =
    global::System.Data.AcceptRejectRule.None;
    fkc.DeleteRule =
    global::System.Data.Rule.Cascade;
    fkc.UpdateRule =
    global::System.Data.Rule.Cascade;


    Dave
    Sunday, May 31, 2009 7:43 PM
  • Constraints are not enforced unless the EnforceConstraints property is set to true.

    fkc.AcceptRejectRule = global::System.Data.AcceptRejectRule.Cascade ;
    ds.EnforceConstraints = true ;


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Sunday, May 31, 2009 8:07 PM
    Sunday, May 31, 2009 7:57 PM
  • In your case, it might make sense to remove the constraint temporarily, update the parent name, then add the constraint back.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, May 31, 2009 8:17 PM
  • The problem is not on the ADO.NET side, it is on the SQL Server side that I get the concurrency errors saying 0 rows where updated when I try to update the child row as I suspect that even inside of a transaction, the server side constraints and cascading has already taken effect and fails the child update. Besides, I did try that even though I did not think likley to help and it didn't. Thanks though...Dave
    Dave
    Sunday, May 31, 2009 10:13 PM
  • Perhaps you should then ask your question in the Sql Server forum

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, May 31, 2009 10:18 PM
  • Good idea but I'm still hoping someone in the ADO.NET world knows whether ADO.NET should be able to update a parent/child pair of rows where the database has constraints and cascading defined. If not, I don't know why the import wizard should import the relationship constraints without complaining as it would seem to be a bit of a deadlock situation...Dave
    Dave
    Sunday, May 31, 2009 11:04 PM
  • Certainly you can do this, but you have to write the logic for this. All the ForeignKeyConstraint does in the disconnected world is:

    A ForeignKeyConstraint restricts the action performed when a value in a column (or columns) is either deleted or updated. Such a constraint is intended to be used with primary key columns. In a parent/child relationship between two tables, deleting a value from the parent table can affect the child rows in one of the following ways.

    • The child rows can also be deleted (a cascading action).

    • The values in the child column (or columns) can be set to null values.

    • The values in the child column (or columns) can be set to default values.

    • An exception can be generated.

    http://msdn.microsoft.com/en-us/library/system.data.foreignkeyconstraint.aspx

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, June 1, 2009 12:23 AM
  • That's exactly what I'm depending on from ADO.NET datasets and what works fine in my application. That when I delete the parent I want the child to be deleted as well. And renaming the parent primary key [the TaskDefinition name in my case], I want the child's foreign key reference back to the parent to be renamed else I lose referential integrity. And that works in my application and within my dataset's datatables' hierarchial grid views just fine. It's updating the parent and child row pair back to the database that fails. I guess I'm just trying to get verification that if you enforce the FK constraints and cascading updates and deletes within ADO.NET, then you can't also have them in the database as by the time you push the child row back, it has already been deleted or changed due to having just deleted or pushed the parent row back. If that should not be the case then I'm doing something wrong. If that is the case, then it seems weird that the typed dataset wizard would import the relationships and cascading definitions when it would not be possible to use them for updating or deleting a parent/child relationship. And even without the typed dataset, if you want to show and update a hierachial view of the related tables, you cannot enforce FK constraints and cascading together at both the ADO.NET side and the database. Or maybe I'm just misunderstanding this whole thing. I notice MSFT's AdventureWorks database has all the FK constraints but no cascading. Perhaps that is why??? Dave
    Dave
    Monday, June 1, 2009 3:46 AM
  • The cascading only does what I have highlighted above. That is all.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, June 1, 2009 2:35 PM
  • John, I don't understand why you are talking about the cascading doing what it does. I already understand that those are the choices and I in fact take advantage of the dataset constraint support for delete and update cascading and all that works fine and as I would expect within my hierarchical grid view application. It just seeems to me that FK constraints and cascading are mutually exclusive between having them in the database and the ADO.NET client if you expect to be able to push the changes back to the database. I'm just looking for verificaion if that is true or not and it sort of makes sense that it is true but if so odd they would support it in the designer without a warning. Thanks, Dave
    Dave
    Monday, June 1, 2009 3:55 PM
  • After learning how to do SQL profiling, I think this is a bug in the Typed Dataset code. The following is a trace from a transaction that updates 2 related tables [parent=tb_TaskDefinitions and child=tb_Tasks] where I renamed the primary key [DefName] in the first table which also renamed the foreign key reference [DefName] in the second table. Problem is that even though the dataset KNOWS that FK relationships are enforced as well as cascading updates in both the database and the ADO.NET Dataset, it still uses the ORIGINAL foreign key value for Defname when verifying the child row it is trying to update when it should be using the CURRENT foreign key value for DefName. I have highlighted the syntax in quesiton. I plan to start a new thread investigating this as a bug! Thanks, Dave

    The first update inside transaction works OK and updates the parent row changing the DefName from "CPU Task" to "CPU Taskxxx":

    exec sp_executesql N'UPDATE [tb_TaskDefinitions] SET [DefName] = @DefName, [DefDesc] = @DefDesc, [DefDisable] = @DefDisable, [DefDebug] = @DefDebug, [DefEventType] = @DefEventType, [DefClearTime] = @DefClearTime, [DefEvtMessage] = @DefEvtMessage, [DefAssembly] = @DefAssembly, [DefLocation] = @DefLocation, [DefXmlSchema] = @DefXmlSchema, [LastUpdate] = @LastUpdate, [LastUserid] = @LastUserid WHERE (([DefName] = @Original_DefName) AND ([DefDesc] = @Original_DefDesc) AND ([DefDisable] = @Original_DefDisable) AND ([DefDebug] = @Original_DefDebug) AND ([DefEventType] = @Original_DefEventType) AND ((@IsNull_DefClearTime = 1 AND [DefClearTime] IS NULL) OR ([DefClearTime] = @Original_DefClearTime)) AND ([DefEvtMessage] = @Original_DefEvtMessage) AND ([DefAssembly] = @Original_DefAssembly) AND ((@IsNull_DefLocation = 1 AND [DefLocation] IS NULL) OR ([DefLocation] = @Original_DefLocation)) AND ((@IsNull_DefXmlSchema = 1 AND [DefXmlSchema] IS NULL) OR ([DefXmlSchema] = @Original_DefXmlSchema)) AND ([LastUpdate] = @Original_LastUpdate) AND ([LastUserid] = @Original_LastUserid));

    SELECT DefName, DefDesc, DefDisable, DefDebug, DefEventType, DefClearTime, DefEvtMessage, DefAssembly, DefLocation, DefXmlSchema, LastUpdate, LastUserid FROM tb_TaskDefinitions WHERE (DefName = @DefName)',N'@DefName varchar(11),@DefDesc varchar(12),@DefDisable bit,@DefDebug bit,@DefEventType int,@DefClearTime int,@DefEvtMessage varchar(9),@DefAssembly varchar(7),@DefLocation varchar(3),@DefXmlSchema varchar(8000),@LastUpdate datetime,@LastUserid varchar(9),@Original_DefName varchar(8),@Original_DefDesc varchar(12),@Original_DefDisable bit,@Original_DefDebug bit,@Original_DefEventType int,@IsNull_DefClearTime int,@Original_DefClearTime int,@Original_DefEvtMessage varchar(9),@Original_DefAssembly varchar(7),@IsNull_DefLocation int,@Original_DefLocation varchar(3),@IsNull_DefXmlSchema int,@Original_DefXmlSchema varchar(8000),@Original_LastUpdate datetime,@Original_LastUserid varchar(9)',@DefName='CPU taskxxx',@DefDesc='Task for CPU',@DefDisable=1,@DefDebug=1,@DefEventType=1,@DefClearTime=5,@DefEvtMessage='$computer',@DefAssembly='cpu.dll',@DefLocation='c:\',@DefXmlSchema=NULL,@LastUpdate='2009-05-30 00:00:00',@LastUserid='win7\dave',@Original_DefName='CPU task',@Original_DefDesc='Task for CPU',@Original_DefDisable=1,@Original_DefDebug=1,@Original_DefEventType=1,@IsNull_DefClearTime=0,@Original_DefClearTime=5,@Original_DefEvtMessage='$computer',@Original_DefAssembly='cpu.dll',@IsNull_DefLocation=0,@Original_DefLocation='c:\',@IsNull_DefXmlSchema=1,@Original_DefXmlSchema=NULL,@Original_LastUpdate='2009-05-30 00:00:00',@Original_LastUserid='win7\dave'

    Second Update inside transaction fails trying to change DefName from "CPU Task" to "CPU Taskxxx" because it has already been changed by the previous update with the ADO.NET TableAdapter full well knowing that the value changed since it has the FK constraint and cascading in effect on the client and imported that fact from the database using the Typed Dataset wizard:

    exec sp_executesql N'UPDATE [dbo].[tb_Tasks] SET [TskName] = @TskName, [DefName] = @DefName, [TskDesc] = @TskDesc, [TskDisable] = @TskDisable, [TskDebug] = @TskDebug, [TskClearTime] = @TskClearTime, [TskEvtMessage] = @TskEvtMessage, [TskXmlConfiguration] = @TskXmlConfiguration, [TskOutputLocation] = @TskOutputLocation, [LastUpdate] = @LastUpdate, [LastUserid] = @LastUserid WHERE (([TskName] = @Original_TskName) AND ([DefName] = @Original_DefName) AND ([TskDesc] = @Original_TskDesc) AND ([TskDisable] = @Original_TskDisable) AND ([TskDebug] = @Original_TskDebug) AND ((@IsNull_TskClearTime = 1 AND [TskClearTime] IS NULL) OR ([TskClearTime] = @Original_TskClearTime)) AND ([TskEvtMessage] = @Original_TskEvtMessage) AND ((@IsNull_TskOutputLocation = 1 AND [TskOutputLocation] IS NULL) OR ([TskOutputLocation] = @Original_TskOutputLocation)) AND ([LastUpdate] = @Original_LastUpdate) AND ([LastUserid] = @Original_LastUserid));

    SELECT TskName, DefName, TskDesc, TskDisable, TskDebug, TskClearTime, TskEvtMessage, TskXmlConfiguration, TskOutputLocation, LastUpdate, LastUserid FROM tb_Tasks WHERE (TskName = @TskName)',N'@TskName varchar(11),@DefName varchar(11),@TskDesc varchar(12),@TskDisable bit,@TskDebug bit,@TskClearTime int,@TskEvtMessage varchar(9),@TskXmlConfiguration varchar(3),@TskOutputLocation varchar(3),@LastUpdate datetime,@LastUserid varchar(9),@Original_TskName varchar(11),@Original_DefName varchar(8),@Original_TskDesc varchar(12),@Original_TskDisable bit,@Original_TskDebug bit,@IsNull_TskClearTime int,@Original_TskClearTime int,@Original_TskEvtMessage varchar(9),@IsNull_TskOutputLocation int,@Original_TskOutputLocation varchar(3),@Original_LastUpdate datetime,@Original_LastUserid varchar(9)',@TskName='CPU task 80',@DefName='CPU taskxxx',@TskDesc='Task for CPU',@TskDisable=0,@TskDebug=0,@TskClearTime=4,@TskEvtMessage='$COMPUTER',@TskXmlConfiguration='abc',@TskOutputLocation='abc',@LastUpdate='2009-05-30 00:00:00',@LastUserid='win7\dave',@Original_TskName='CPU task 80',@Original_DefName='CPU task',@Original_TskDesc='Task for CPU',@Original_TskDisable=0,@Original_TskDebug=0,@IsNull_TskClearTime=0,@Original_TskClearTime=4,@Original_TskEvtMessage='$COMPUTER',@IsNull_TskOutputLocation=0,@Original_TskOutputLocation='abc',@Original_LastUpdate='2009-05-30 00:00:00',@Original_LastUserid='win7\dave'

    Tuesday, June 2, 2009 5:39 PM
  • I'm of the opinion this is a bug in the Dataset/TableAdapter support from Typed or plain Datasets. I have posted a workaround here http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/0fc1128e-6e99-42cf-8862-5aae0bb758f0

    - Dave
    Tuesday, June 2, 2009 11:02 PM
  • I have exactly the same problem.

    Are there no way to make an automatic master detail table update?

    I think it should be easy and with no code.

    Thursday, June 10, 2010 8:58 PM
  • I made a workaround:

    1. configure 'SQL Server' to make the cascade update.

    2.in the data set the relations are 'Relation Only'  (no cascade)

    3. Make TableAdapter.Update (it makes the cascade with SQL Server)

    4. Fill the Data Set table again so it reads the changes made by SQL Server

    It works but I think is not a best practice.

    Thursday, June 10, 2010 9:37 PM