I’ve got a table with the following structure :
Col1 | Col2 | Col3
A | B | C
A | C | B
E | D | C
The primary key is (Col1,Col2,Col3).
I get the data in another project and display it in view with a grid.
When the user edit a row in the grid, for example : A,B,C -> A,B,E, I get (A,B,E) and try to update the database. But I don’t know which row must be updated if I don’t have the original (A,B,C).
What’s the proper way to resolve this issue ? Should I create a temporary ID on my second project ? I don’t think create an specific ID in the database is a good idea.
4
This is one of the many instances where you should embrace the lesser evil that is Surrogate Keys. Whichever table has a primary key of (col1,col2,col3) should have an additional key created by the system, such as an identity or GUID.
You don’t specify the data type of (col1,col2,col3), but if for some reason you’re allergic to surrogate keys you can embrace the slightly greater evil of a “combined key”, where instead of a database-created value your unique key field is derived from some other fields. (In this instance, it’d be something like CONCAT(col1, '-', col2, '-', col3) ).
Should neither of the above be practical, you will be left with the greatest evil of having to manually specify all three columns each time you query a record. Which means that any other object or table which references this one will need to have not one but three distinct fields to identify which record you’re talking about.
Ideally, btw, you would have some business key in the actual data which you can guarantee by design will be unique, never-changing, and never-blank. (Or at least changing so infrequently that the db can handle cascade updates reasonably well.)
You may wind up using a surrogate key for performance in such a case anyway, but that’s an implementation detail rather than a data modeling requirement.
4
To update a row, you need to know which row to update – in this case you must store the original key somewhere. You could duplicate the key data in hidden columns in your grid control, or you could associate the a triple (or a struct) of the data with every row.
What is important is that the key for each row is saved somewhere that cannot be modified by the user (or even seen by the user). This will apply just as much if you use an identity column on your DB- you’ll still need to store it, not let the user modify it, and use it to refer to the row that is changed.
When you update, you must refresh the key in your row – if you’re using an id column this will not change, but if you use the 3 columns then as the user changes the data in them the DB gets updated but the key referenced in your grid must be updated too – think what happens if the user changes them again.