none
Update without primary key potential problems? RRS feed

  • Question

  • I am desiring to update a database using the following:

     

    Me.Validate()

    Me.CLIENTBindingSource.EndEdit()

    Me.CLIENTTableAdapter.Update(Me.DsClientsMain.CLIENT)

     

    However, the Client table does not contain a primary key, although the ID's in the table are unique, and I am unable to add the primary key to the database. 

     

    I found this thread which suggests that although the wizard is unable to create the update query without a primary key that I can make it manually. 

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=231917&SiteId=1

     

    Using the database in the project folder I set the primary key in the Client table and generated the SELECT and UPDATE queries.  I left the primary key on the database in the project folder, but I removed the primary key from the database in the debug folder.  I set the copy to output directory to never.  (i can only modify my test database and am unable to update the live database with a key)  I am now able to use the above code and it has been working without any noticable issues.

     

    My dataset is filled with only one record at a time using the following code, so when I use the UPDATE from above I am only updating at most, one record.

     

    Dim clientID As String = textbox1.text

    Me.CLIENTTableAdapter.Fill(Me.DsClientsMain.CLIENT, clientID)

     

    Is there anything I'm missing?  Although this works I am unsure of what problems *might* happen from using the UPDATE query on a database without a primary key.  This application is going to be used in a multiuser environment on a terminal server.

    Saturday, July 7, 2007 7:14 PM

All replies

  • I would suggest first that you may want to use a fresh copy of your live database without any modifications to make sure that you haven't unintentionally changed your test environment.  Secondarily, I would be concerned most about updates based on a value in the table that has no uniqueness constraint on it.  If this constraint is somehow broken, you will not get errors on updates, but will be updating multiple records with the same data, which would, assumedly, corrupt one or more of the records you are updating.  This all depends, of course, on how certain you are that those values are unique.  There should be no possible way to update them once they are created, and they should be created in such a manner as to guarantee unique keys without duplication in all circumstances, including stress scenarios.  Without that primary key constraint, you have less margin for error because the server will not be double checking that constraint for you.

     

    Be careful,

     

    John

    Sunday, July 8, 2007 1:45 AM
  • Even though the table doesn't have a primary key, the client field is unique so I'm not worried about doubling up.

     

    Thanks for the feedback.

     

    Wednesday, July 11, 2007 2:44 AM