CODEWITHSUNDEEP
mysqlsqlpostgresql
Jimmy
Jimmy

Reputation: 12487

Removing items from database but keeping uniqueID

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.

Upvotes: 0

Views: 65

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95542

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.

Upvotes: 1

Related Questions