none
The INSERT statement conflicted with the FOREIGN KEY constraint

    Question

  • Hi i've configured my tableAdapters CRUD commands with stored procedures. but when i want to insert relational data via TableAdapterManager.UpdateAll method, i got the following exception : The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "Test2", table "dbo.Customers", column 'CustomerID'. The statement has been terminated. if i use int dataType as Pk in my tables (in sql server) i got this error : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records but when i use uniqueidentifier as PK in tables and tableAdapter CRUD commands configured with text, it works, but if tableAdapter commands configured with stored procedures, i got the first error. why and how to fix that ? Note : i think this maybe insert command for child table needs to PK of inserted parent record as FK, is this correct ? if yes, how to fix this problem ? Thanks
    this is my Signature
    Friday, April 24, 2009 3:57 PM

All replies

  • My guess is that you might want to look into using Nullable<int> instead of int.  The default value for int is 0, and if you're trying to insert a row where you haven't set the primary key, the computer is going to think the primary key is 0, and if another row in the database already has 0 set as it's primary key, you'll get this error.  UniqueIdentifier, or GUID, is going to be generating a new GUID each time you instantiate the class, so you won't run into this problem.  The integer value should be null until after you update the database. 

    David Morton - http://blog.davemorton.net/
    Friday, April 24, 2009 4:06 PM
  • If your primary key is an autonumber which has no business meaning, then the INSERT statement doesn't require you to add that in the SQL statement as the RDMS will handle that. The foreign key cannot be left empty since it is contrained to the other table and must be populated. Are you supplying the value for the foriegn key field?
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Friday, April 24, 2009 7:11 PM
    Friday, April 24, 2009 4:15 PM
  • Thanks David & John
    my problem about Concurrency Violation was solved, because i've use GUID as PK instead of int (or autonumber), but in visual studio, when i create TableAdapters and configure CRUD commands with stored procedures, i got the first error, wheras when i configure TableAdapter's CRUD commands with T-Sql command texts, it works. i'm looking for solving this problem.
    do u have a idea ?
    this is my Signature
    Friday, April 24, 2009 8:45 PM
  • Keep in mind that GUID's are not as efficient as autonumbers if speed is a concern.

    Can we see your code for your CRUD for the SP's
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, April 24, 2009 8:49 PM
  • Hi again
    i uploaded my vs project and db example to this location.
    1. the project is Visual Studio 2008 and .net 3.5.
    2. plz create a sample database named : Test2 and then run Test2-Db.Sql
    i'm waiting for your reply.
    Thanks
    this is my Signature
    Friday, April 24, 2009 9:24 PM
  • I took a look at your project.

    Let me first say that you should encapsulate the Data access layer into a class rather then having multiple forms containing its own data access functionality. Also, I am not too hip on using "TableAdapters" but prefer to stick to DataAdapters. So perhaps someone else can chime with more experience with using them.

    The TableAdapter is designed to expose a strongly typed structure to manage rowsets extracted from a specific Table or a JOIN. If you only access a single table in the SELECT, the code-generators can also add the CRUD to change the table. The DataAdapter is designed to hold several rowsets from several queries (or JOINs) and permit you to define artificial relationships between the DataTable objects. It's updatable if the SelectCommand points to a single table.

    Some have equated the TableAdapter as a DataAdapter with training wheels - something to get started with but certainly nothing to finish with.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, April 25, 2009 12:29 AM
  • Thanks John
    do u have idea ?
    what's your suggestion ? can u give me a sample source code ?
    thanks

    this is my Signature
    Saturday, April 25, 2009 10:59 AM
  • As you are architecting the DataLayer class ask yourself these questions.

    1.) Are the updates going to be done one by one or will it be a cached collection of changes that will be propagated back to the database.
    2.) How many tables will be effected by inserts/deletes. Will a delete or insert in one table affect another constrained table? Will this be handled by triggers in the database or are you writing your own logic to handle that. In a disconnected world [DataSet/DataTable(s), DataRelation, etc..], knowledge of #1 will determine the outcome of #2.
    3.) How sophisticated is your relational database?
    4.) How many users will be using your app at once? Will it be set up that each installation will be sharing the database on some intranet, or will it be distributed so each user will have their own dataset?

    As a side note, this question would be more appropriately asked in either of these forums:
    ADO.NET DataSet
    Windows Forms Data Controls and Databinding


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Saturday, April 25, 2009 2:06 PM typo
    Saturday, April 25, 2009 2:02 PM
  • Thanks John
    my question is not about software architecture or encapsulating data access layer, my question is about how to avoid this problem ?

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "Test2", table "dbo.Customers", column 'CustomerID'. The statement has been terminated
    thanks
    this is my Signature
    Saturday, April 25, 2009 6:43 PM
  • Thanks John
    my question is not about software architecture or encapsulating data access layer, my question is about how to avoid this problem ?

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "Test2", table "dbo.Customers", column 'CustomerID'. The statement has been terminated
    thanks
    this is my Signature

    If you do like to be able to use UpdateAll method, Identity column is not usefull, becuase you will know the id of newly created Customer when the insert of the row finished. I will suggest to do the operation of adding customer in database before client can add any order for that customer.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Saturday, April 25, 2009 7:57 PM
  • Thanks John
    my question is not about software architecture or encapsulating data access layer, my question is about how to avoid this problem ?

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "Test2", table "dbo.Customers", column 'CustomerID'. The statement has been terminated
    thanks
    this is my Signature

    If you do like to be able to use UpdateAll method, Identity column is not usefull, becuase you will know the id of newly created Customer when the insert of the row finished. I will suggest to do the operation of adding customer in database before client can add any order for that customer.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/

    Thanks boban
    if i must manage master-detail update commands manually, why microsoft introduced TableAdapterManager ? i think this class implement this logic (update master table before child table)
    but how to do that correctly, if not, i think it has a bug!

    i want use TableAdapterManager.UpdateAll for update multiple tables (also master-detail tables) because it update all updates within a single transaction, i can perform this action(s) via TransactionScope class, but i want to use lightweight transaction which provides in .net core and don't use MsDtc.

    how u update master-detail tables in your softwares ?

    this is my Signature
    • Proposed as answer by IT-Programming Wednesday, March 30, 2011 5:06 PM
    Saturday, April 25, 2009 8:35 PM
  • By my understanding Creating Customer and also orders shouldn't be one transaction.
    If you use TableAdapter to update all tables in order to execute all actions in one transaction, then your reason is wrong.
    All this can be done manually, opening transaction, do all actions that need to be in one transaction, and close commit the thansactions.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Saturday, April 25, 2009 8:53 PM
  • Hi boban.s
    as u can see in this page , the following lines demonstrate that TableAdapterManager.UpdateAll method perform updates in a single transaction :

    When you save data (by calling the TableAdapterManager.UpdateAll() method), the TableAdapterManager attempts to update the data for each table in a single transaction. If any part of the update for any table fails, the whole transaction is rolled back

    and as u can see in this page , TableAadpterManager provide insert,update,delete actions for related tables without violating the foreign-key constraint :

    The TableAdapterManager uses the foreign-key relationships that relate data tables to determine the correct order to send the Inserts, Updates, and Deletes from a dataset to the database without violating the foreign-key constraints (referential integrity) in the database

    but in action, it did not provide this feature for me ?
    what about u ?
    can u get me an example of TableAdapterManager ?
    Thanks
    this is my Signature
    • Edited by Hamed_1983 Saturday, April 25, 2009 9:25 PM
    Saturday, April 25, 2009 9:24 PM
  • I never used TableAdapterManager because i do those kind of things manually. I mentioned before that operation that you want to do, doesn't suppose to be one transaction. I suppose that using tableadaptermanager in that scenario is not out of the box when you use stored procedures. How do you think is posible that tableadaptermanager will discover the new identity value generated in Customer table. I can gues that when using stored procedures, at least you need to provide a return value for primary key retrieved from SCOPE_IDENTITY() as an output parameter from stored procedure.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Sunday, April 26, 2009 8:38 AM
  • Hi bobans
    as u can see in my example, stored procedures for insert operation return inserted PK and i thought it should be handle by TableAdapterManager. however, can u give me an example of Update related tables with stored procedures ?
    thanks
    this is my Signature
    Sunday, April 26, 2009 9:19 PM
  • Can you first delete the post that have lots of space and breaks the page.

    I never seen your code or stored procedure. Even if you do post the code this kind of problem without having it in a project is hard to discover.
    Here
    is a working example that uses SQL statements. I have created a sample project that uses stored procedures and that work with return parameters and also with return statement that return new idenetity value. Both cases fails with same problem like you have. It's posible that this is a bug or maybe TableAdapterManager doesn't support this feature yet.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Monday, April 27, 2009 12:19 PM
  • Can you first delete the post that have lots of space and breaks the page.

    I never seen your code or stored procedure. Even if you do post the code this kind of problem without having it in a project is hard to discover.
    Here
    is a working example that uses SQL statements. I have created a sample project that uses stored procedures and that work with return parameters and also with return statement that return new idenetity value. Both cases fails with same problem like you have. It's posible that this is a bug or maybe TableAdapterManager doesn't support this feature yet.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/


    Hi boban
    almost i found this problem.
    i designed my tables relational in sql server and it have FK & PK, but in visual studio dataSet designer, we don't use original tables, we use stored procedures, hence visual studio don't know anyThing about FK & it's Relations.

    Solution :
    If u create a relation only between dataTables in your dataSet designer, the problem solved! if u can, try this and write the result here.


    this is my Signature
    Monday, May 18, 2009 5:37 AM
  • Yes i tested the scenario having tables drop-ed on dataset designer instead of strored procedures for select and it worked. But when i tried with stored procedures not. I don't have that much time to descover why but if you do you can inspect the generated code from designer and see the diference between two cases. When you find the bug in the code you will have just the confirmation of our conclusion that under this scenario you can't make it work. You have to find another way of doing it, either with working way using tables or do it separately. Also please go to connect site and if this bug is not already there please add it and send me the link to add my wote.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Monday, May 18, 2009 7:24 AM
  • Yes i tested the scenario having tables drop-ed on dataset designer instead of strored procedures for select and it worked. But when i tried with stored procedures not. I don't have that much time to descover why but if you do you can inspect the generated code from designer and see the diference between two cases. When you find the bug in the code you will have just the confirmation of our conclusion that under this scenario you can't make it work. You have to find another way of doing it, either with working way using tables or do it separately. Also please go to connect site and if this bug is not already there please add it and send me the link to add my wote.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/

    Hi boban.s
    If u use Stored Procedures in DataSet Designer, ensure your entities have relation only together (u must create relation between entities manually), then test it, the problem must be solved.
    plz do it and post the result here.
    thanks
    this is my Signature
    Monday, May 18, 2009 8:48 PM
  • Actually, I think it's a bug in MS generator. I had this error too, I found that there was a wrong order of TableAdapters to be updated generated (UpdateAll thus wanted to insert rows child table using its adapter and then master table, causing foreign key constraint error in DB). Switching to 'relation only' of course did not help, I had Identity columns in DataSet which needed to be cascaded to child tables and if it didn't, the constraint error would raise in SQL database, not dataset.

    What did help, was cutting one of the tables in DataSet, and pasting it back, which after save caused regenerating TableManager code in right order.
    So I suppose it's a bug and it's not fixed in VS2010Beta2.
    Wednesday, March 10, 2010 3:59 PM
  • The INSERT statement conflicted with the FOREIGN KEY constrainnt."FK_Orders_Customers". The conflict occurred in database "Test2", table "dbo.Customers", column 'CustomerID'. The statement has been terminated.

    The problem here can be found actually in the SQL Programability /Stored Procedure  or in the *DataSet.xsd. First take a look at the statement for the UPDATE and INSERT statements of the Orders Table. It is probable missing the @CustomerID int, [CustomerID] from the statement. The form needs to know the Foriegn ID of CustomerID to be placed in the Stored procedure or the ORDER Table.

    This is how you fix this ERROR clause hope it helps future programmers cause the other response on this mater did not help at all were to begin the search or even look for a solution.

    • Proposed as answer by IT-Programming Wednesday, March 30, 2011 5:19 PM
    Wednesday, March 30, 2011 5:19 PM