how to reduce primary key by one after deleting a record

[arsalanghouri]

how to reduce primary key by 1 after deleting records

[Mitja Bonca]

Why would you change the primary key? You shouldn`t do that. Thats why Primary keys are for - they stay the same until forever. What in case if this table of yours is connected to some other table, abnd there is a foreign key? you will get different data when you will try to equl both primary key and foreign key.
So I would never do this.
One more thing to add: if you will change ids why are they for anyway? They are pointless to use.

[zachattack05]

arsalanghouri;

Mitja is right.

Primary keys that are integers and have the identity property cannot be reduced. One of the best database design documents I ever found was from a website called "The Access Web" (not sure if it's still around) and they had a 10 commandments of database design. One of them was (edited to apply to SQL): "Thou shalt not use identity primary keys if the field is meant to have meaning for thy users." The key (number) is only there to identify that specific record/row in the database.

If you wanted, you could use a natural key instead of a surrogate key. This would allow you to change the key value and "cascade" that change (if you set the relationship up that way), but in reality, primary keys shouldn't change and outside of the database's relationships, shouldn't mean much to anyone so there's no reason to change the key anyway.