Possible to condense primary key/serial?

Question

Hello, I have a database where everything is linked with foreign keys, so the Postgres knows exactly how the database is layed out..

Well, Lets say I have Table1 and Table2.

Table1 has 3 fields. RID, table2_rid,data

So table1.table2_rid references table2.RID and this is expressed with a foreign key. In both the RID field is the primary key and is a serial type.

What I would like to know is how to "condense" the primary keys? Like say you add 5 records and deleted record number 3. Your primary keys would look like

1
2
4
5 

Well, how do I get to update everywhere so that the primary key(and corresponding foreign keys) can be condensed into

1
2
3
4

Answer 1

Look into on update cascade and on delete cascade.

create table table_1 (
    id integer
        primary key,
    name char(30)
);
create table table_2 (
    id integer
        primary key,
    table_1_id integer
        references table_1
        on update cascade
        on delete cascade,
    detail char(30)
);

Answer 2

It's best if a primary key never changes: Renumbering them is a PITA.

If you need an ID for humans, one that has no gaps, A. Elein Mustain shows how to create a gapless sequence.

Answer 3

Except for very rare scenarios, gaps in the PK sequence are just right, to intent to get rid of them is a bad idea.