How do I increase all of my
EmployeeID values by one if it is the primary key?
|show 5 more comments|
I assume that this question is about how to update a bunch of rows to a value on higher without stepping on each other. If you update id=1 to id=2 in the first row while the second row (with id=2) is still there you will get a primary key violation because now you have two rows with id=2.
To prevent that type of collision you just need to start at the largest value and increase it by one. that opens up a gap for the second largest value to move into which in turn opens a gap for the third largest value to move into, and so on.
In SQL Server however, you need not worry, as the engine is taking care of that automatically:
MS SQL Server 2008 Schema Setup:
If you actually follow the SQL Fiddle link, you can also see the execution plans for the two update statements. The first one looks like this:
There you can see fairly far to the left a
An added benefit (and the original reason why this behavior was implemented) is that this two step approach provides Halloween Protection.
Without more detail about the structure of your database there are a large number of variables here.
If any other table that contains the employee IDs as reference to this table and forgeign keys are properly setup and set to
If you have tables that refer to the employee ID without forgeign key constraints being present then you will have to update those too with
I would question why you would ever want to do this though. An employee identifier is generally a surrogate key (also called a pseudo key) that carries no meaning aside from identifying a given person (it could eaily be a UUID insead of a number, except for the fact it is likely used in real-world written down, so a UUID would not be convenient). See the "psuedo-key neat-freak" chapter in SQL Antipatterns for more discussion on this.