patternsqlModerate
MySQL: Swap ID between two rows
Viewed 0 times
rowsswapmysqlbetweentwo
Problem
We have existing table:
Column
But MySQL logically throws out the error.
I know it is stupid scenario, but we have a customer who wants it that way. He want to edit/update each existing records.
| id | name | color | calories |
------------------------------------------
| 1 | apple | red | 20 |
| 2 | orange | orange | 10 |
| 3 | grapes | green | 5 |
| 4 | bananas | yellow | 15 |
| 5 | plum | purple | 25 |
------------------------------------------Column
id is unique and primary key. I'm facing a problem with edit/update records when, i heed to change for example in row 3 where, id = 3 to id = 5 and row 5 where, id = 5 to id = 3... Like swap id between rows 3 and 5. Of course there are duplicate, but how to resolve this scenario...But MySQL logically throws out the error.
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'I know it is stupid scenario, but we have a customer who wants it that way. He want to edit/update each existing records.
Solution
Column id is unique and primary key ... Like swap id between rows 3 and 5.
You should not do this.
The Primary Key of any record should be created/set/generated when the record is first created and it should never change, not for the entire lifetime of that record, right up to the point when the record is finally deleted.
Imagine the chaos if banks renumbered people's bank accounts!
Also, while you don't appear to have them here (yet), one of the main benefits of having a Primary Key is that other tables can have Foreign Keys that refer to it. If you start changing the Primary Key value, then you have to change all the corresponding Foreign Key values as well (the database will enforce this).
I seriously doubt any User will understand this.
... customer ... want to edit/update each existing records.
By all means allow them to edit the data, just not the Primary Key.
I'm going to assume that you're providing some sort of UI for this and that they're not issuing SQL directly against the database.
If they're trying to use it as some sort of sequence or "order", then using a separate column is the way to go. Primary Keys, even auto-increment ones, are guaranteed to be unique; they are not guaranteed to be sequential or contiguous.
You should not do this.
The Primary Key of any record should be created/set/generated when the record is first created and it should never change, not for the entire lifetime of that record, right up to the point when the record is finally deleted.
Imagine the chaos if banks renumbered people's bank accounts!
Also, while you don't appear to have them here (yet), one of the main benefits of having a Primary Key is that other tables can have Foreign Keys that refer to it. If you start changing the Primary Key value, then you have to change all the corresponding Foreign Key values as well (the database will enforce this).
I seriously doubt any User will understand this.
... customer ... want to edit/update each existing records.
By all means allow them to edit the data, just not the Primary Key.
I'm going to assume that you're providing some sort of UI for this and that they're not issuing SQL directly against the database.
If they're trying to use it as some sort of sequence or "order", then using a separate column is the way to go. Primary Keys, even auto-increment ones, are guaranteed to be unique; they are not guaranteed to be sequential or contiguous.
Context
StackExchange Database Administrators Q#297565, answer score: 17
Revisions (0)
No revisions yet.