#30 Non-primary, unique fields cause problems!

[spingary]

Editing a table with multiple unique keys can cause the
record being edited to be deleted!

Scenario:
A table has an integer primary key "ID", and a unique key
called "username" (so no two records can have the same
username), and then a third field called "Address"

If I edit an existing record's address, the edit logic
currently does this:
1) adds a temp record with the same ID, username, and
new address -- this fails (since we are trying to create a
record with the same username) but there is no logic to
catch this. Current code assumes this is OK.

2) check permission to make sure we can edit this
record which checks out ok so then it...

3) DELETES the original record (bye bye data!)

4) UPDATES temp record (which doesn't exist) and sets
the primary key to the original record's. Again, no check
for affected rows so everything comes back happy, but
the row is gone!

This logic seems a bit funny - maybe it should be
revised, since it also costs a new autonumber to be
skipped (doesn't it?)

Until this is fixed/amended, don't assign any other unique
keys to your table or you will lose data (like I did).