Removing items from database but keeping uniqueID

Question

I am new to databases. I have an SQL database which looks a bit like this:

BEGIN;
CREATE TABLE "country" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL
)
;
CREATE TABLE "location" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL,
    "coordinate" varchar(255) NOT NULL,
    "country_id" integer NOT NULL REFERENCES "country" ("id")
)
;
CREATE TABLE "item" (
    "id" integer NOT NULL PRIMARY KEY,
    "title" varchar(25) NOT NULL,
    "description" text NOT NULL,
    "date" datetime NOT NULL,
    "source" varchar(255) NOT NULL,
    "link" varchar(255) NOT NULL,
    "location_id" integer NOT NULL REFERENCES "location" ("id")
)
;

If I delete item entries from the database, and then add new item entries to the database, what happens about the items primary key? I mean, will it just increment, or will it fill in any gaps?

In my situation I cant keep unneeded entries in the database as "active=no" entries, I need to delete them completely, but I am concerned about it messing up the primary keys on the items.

"but I am concerned about it messing up the primary keys on the items" --- any technical reason for that? — zerkms, Dec 02 '12 at 22:58
I guess I should, although I dont know how to set up an SQL environment yet. I always like to understand the theory too though. — Jimmy, Dec 02 '12 at 22:58
Well, if I have the entry 1,2,3,4,5,6,7 and I delete entry ID=4, then when I try to add a new entry will it take up 4 or be 8? Should the primary key be in a separate table to avoid this? — Jimmy, Dec 02 '12 at 22:59
See this prior question for detailed coverage of the issue: http://stackoverflow.com/questions/9984196/rails-postgres-does-not-re-use-deleted-ids-but-mysql-does — Craig Ringer, Dec 02 '12 at 23:20
@zerkms: It might matter a whole lot if you're using something that is too primitive to understands in-database FKs (such as MySQL/MyISAM, Rails, a programmer that doesn't set up FKs, ...). But generally, yeah, it shouldn't matter as `id`s should only be assumed to be unique, that they (often) look like numbers and can be sorted is just an accidental implementation detail. — mu is too short, Dec 02 '12 at 23:34

score 1 · Accepted Answer · answered Dec 02 '12 at 23:18

Relatively few applications actually require a gapless sequence. Most of the applications that do require gapless sequences have to do with accounting. If invoices are consecutively numbered, auditors want to account for all of them, and you can't do that with gappy sequences. (If invoice number 30445 can't be produced, is that because it's misplaced, because it never existed, or because someone is entering fraudulent invoices?)

In SQL databases, not just MySQL and not just PostgreSQL, the sequences (autoincrements, serials, bigserials) the dbms produces aren't guaranteed to be gapless. In fact, they're guaranteed to create gaps under well-understood and common conditions.

Those kinds of integers are usually produced during a transaction. If the transaction is rolled back, that integer is thrown away. There's no attempt to use it again. The next successful INSERT into the same table will create a gap.

Deleting rows from such a table doesn't mess up the primary keys. It just leaves a gap. Both the number and the gap are relatively meaningless.

"(If invoice number 30445 can't be produced, is that because it's misplaced, because it never existed, or because someone is entering fraudulent invoices?)" --- does it have something to do with PK? Why not have artificial sequence PK and unique invoice id — zerkms, Dec 02 '12 at 23:21
I wrote about this in detail here: http://stackoverflow.com/questions/9984196/rails-postgres-does-not-re-use-deleted-ids-but-mysql-does — Craig Ringer, Dec 02 '12 at 23:21
@zerkms: A unique invoice id number doesn't guarantee a gapless sequence of invoice id numbers. The takeaway points are that, if your (relatively rare) application needs a gapless sequence, a dbms sequence generator is the wrong tool to generate it. If, on the other hand, you just need autoincrementing id numbers, expect gaps, and don't lose sleep over them. They're meaningless. — Mike Sherrill 'Cat Recall', Dec 03 '12 at 01:28

Removing items from database but keeping uniqueID

1 Answers1