multiple unique indices and update

[kittyyo]

Consider this table:

ID | Number | Name
1 | 10 | X
2 | 11 | Y
3 | 12 | ABC
4 | 13 | X

ID is the primary key, and there is a unique index on Number.

My program loads the table for editing (ID is read-only, Number and Name are modifiable), then checks through each line if any was updated by the user and writes the record back to the database using UPDATE statements (the ID must stay the same).

Consider that the numbers of Y and ABC are exchanged, such that:

ID | Number | Name
1 | 10 | X
2 | 12 | Y
3 | 11 | ABC
4 | 13 | X

When my program executes the query "UPDATE Table SET Number = 12 WHERE ID = 2", the unique index throws an error since ABC (ID=3) still has Number=12.

How could I avoid that and update both rows safely (without first deleting all records)?

Thanks for any suggestions,
Anne

 

[TonyGroves]

Can't see any way raround that except to delete one or both records first.

 

[kittyyo]

That would be bad because the IDs are referenced by other tables with ON DELETE CASCADE.

Isn't there a possibilty to deactivate the unique index for a short time and then reactivate it again? I somewhere read something like that once, but I can't remember where that was.

 

[TonyGroves]

Even better, you could null the Number fields, then renumber them, without violating the unique index. But, that will probably violate your referential integrity.

 

[TonyGroves]

Sorry, you can ignore the second sentence of my last post. The referential integrity you mentioned would not be affected.

 

[kittyyo]

Thanks, that does the job!