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.

| improve this question | | | | |
  • You should use ...where Col1='A' and Col2='B' and Col3='C', or whatever equivalent to this your object-relational mapper offers. – Kilian Foth Jul 10 '14 at 12:05
  • 1
    Totally depends on context. Either create an unique ID in the table that can't change (you don't want this, though it would be the cleanest option). Otherwise just store the old value set and use this together with the updated data to find the record. – thorsten müller Jul 10 '14 at 12:07
  • 1
    Whenever you have to change individual rows you need a key to identify them. If you don't know the key, then you don't know what to update. If you want to identify rows by their position, then you need another column representing that position, which would be also a key. By the way, in SQL Server you can bypass the problem by using INSERT with the ON DUPLICATE KEY clause. – proskor Jul 10 '14 at 12:16
  • Some dbms's have built-in row id's that can reference a row even if no id is defined in the table schema. You might check your dbms's docs to see if yours does. (MySQL does not, SQLite does) – GrandmasterB Jul 10 '14 at 15:07

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.

| improve this answer | | | | |
  • 3
    I'd give you a +1 but, the root problem here is nothing to do with the DB, but that he doesn't store the key in his grid view. – gbjbaanb Jul 10 '14 at 12:40
  • If I had a dollar for every int id in my hibernate classes, I could take a small development team out for a nice dinner. – corsiKa Jul 10 '14 at 15:24
  • 1
    Just remember that if you use a surrogate, you should also create a unique constraint on (col1,col2,col3) if a business rule exists stating that no duplicates should be allowed. – Tulains Córdova Jul 10 '14 at 16:22
  • @gbjbaanb: After re-reading it, I see that he's talking about possibly MODIFYING the @#$@ign key fields. It'd be trivial to store the "original value" of his controls, but that's just masking the underlying problem. – DougM Jul 10 '14 at 16:22

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.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.