Generic way to identify field in table

Question

I am writing a sql editor (sqlite3).

I can display a table and allow users to edit any value in the table but I now need some way of identifying the value editted. The problem is that I don't know what the primary key is and I don't think it's that good an idea to say "update table set a=b where x=123 and y=123 and z=123 and..." for all the fields that aren't "a".

I'm using jquery (and ajax) and php.

Any ideas?

Answer 1

If you don't know what the primary key is (or you don't know if there is an UNIQUE index), you won't have much of a choice : even if using all fields in your where clause, you might update more than one line (the one the user wanted to edit).

You really need some way to identify one precise line -- and that way is the primary key.

Maybe, just out of curiosity, you my check how phpMyAdmin does that ?
(I know it's not SQLIte, my MySQL -- but maybe the general idea could be re-used ?)

Answer 2

You could force the user to specify a primary key (or at least a UNIQUE) and then retrieve it with SHOW INDEX FROM table (http://dev.mysql.com/doc/refman/5.0/en/show-index.html)

Answer 3

If you cannot determine PK or UK column then you have to use "where x=123 and y=123 and z=123", but remember to add LIMIT 1 - then you are sure you don't edit more than one record.

Answer 4

It is indeed not such a good idea to issue that update '... for all the fields that aren't a'. You should include a too, along with the old value of a in the row that was edited.