4

I have an auto-increment primary key in a SQL table lets say table look like this:

CREATE TABLE [Table] (--Identifier contains a space and uses a reserved keyword.
    [ID] [int] IDENTITY(1,1) NOT NULL ,
    [Name] [varchar](50) NULL, 
    CONSTRAINT [PK__Table] PRIMARY KEY CLUSTERED ([ID] ASC)
);

ID  |  Name|
1     John
2     Jack
3     Bill 
4     Joe

Then I delete row 2 Jack:

ID  |  Name|
1     John
3     Bill 
4     Joe

And what I want to achieve is to change id column so the table will look like this

ID  |  Name|
1     John
2     Bill 
3     Joe

Is there a way to do it?

Shaido
  • 27,497
  • 23
  • 70
  • 73
ziker
  • 160
  • 3
  • 16
  • 4
    Yes there are ways to do it, but as a rule of thumb, I think you shouldn't alter primary keys – vc 74 May 22 '13 at 12:35
  • 1
    if there are referencing records on the primary key, you can't delete and alter it. – John Woo May 22 '13 at 12:37
  • 1
    have you thought what will happen to tables depending upon PK [ID] of this particular table? – Freelancer May 22 '13 at 12:37
  • 1
    http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 but I would suggest not changing a primary key as others have. Ask yourslef why do you care about the numbers being sequential? Does it REALLY matter? probably not so why bother with the update? – xQbert May 22 '13 at 12:39
  • @JW웃, you could disable the constraints, do the update, and re-enable them but it would be a risky operation for little (if any) benefit – vc 74 May 22 '13 at 12:39
  • lets say it is a single table and no other table is connected to this particular table.The problem is that in my java code im highlighting JTable columns according to column id and when some record is deleted it is highlited wrong – ziker May 22 '13 at 12:42
  • 1
    @JurajŽiKoSlavíček do you mean a different style for even/odd rows? if so, use the row number not the primary key – vc 74 May 22 '13 at 12:43
  • @vc74 no its something like desktop livescore application.Im parsing xml feed and when current score in some lets say football match is changed i highlight column with that match – ziker May 22 '13 at 12:46
  • maybe its good to mention that im working with hibernate i forgot to add it to tags – ziker May 22 '13 at 12:49

3 Answers3

4

I will never do that but you can:

  1. create a new autoincrement primary key named ID2
  2. delete ID column
  3. rename ID2 column as ID
tezzo
  • 10,858
  • 1
  • 25
  • 48
0

Quick and dirty way to do it is, (my way) -->

select * into yournewtable from youroldtable order by yourIdentityColumn;

Then, open up yournewtable's design, make sure yourIdentityColumn is Identity(1,1).

Then, drop youroldtable.

Then, rename yournewtable to youroldtable! ta-da!

Jenna Leaf
  • 2,255
  • 21
  • 29
-1
Set identity_insert Table off;
Update Table set ID = 3 where ID = 4;
...
Set identity_insert Table on;

Where Table name is Table

kiks73
  • 3,718
  • 3
  • 25
  • 52