Search code examples
postgresqlforeign-keysprimary-keyauto-increment

Is it possible to condense the primary key/serial?

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

Let's 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? 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
Solution

  • 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.