Antwoord

SEQUENCE (bijvoorbeeld in PostgreSQL en Oracle) is een speciaal databaseobject dat de huidige waarde opslaat en unieke nummers genereert voor invoegen (meestal sleutels). IDENTITY is een eigenschap van een veld die automatisch de volgende op volgorde zijnde waarde toewijst bij invoegen (tabel autoincrement in SQL Server, MySQL, PostgreSQL).

Belangrijkste verschillen:

SEQUENCE is een object buiten de tabel, toegankelijk vanuit verschillende locaties, kan de volgende waarde verkrijgen zonder invoegen (nextval / currval). Kan worden gebruikt voor verschillende tabellen; er kunnen meerdere SEQUENCE voor één tabel zijn.
IDENTITY is onderdeel van de kolomdefinitie, verhoogt automatisch alleen bij het invoegen van een rij.

Voorbeeld:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'Eerste Bestelling');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id wordt automatisch toegewezen

Problemen en nuances:

Als handmatig id's worden ingevoerd, kunnen er "gaten" of overlappingen ontstaan.
Bij het verwijderen van rijen worden waarden niet hergebruikt, wat kan leiden tot een gat in het bereik van sleutels.
In sommige DBMS (bijvoorbeeld MySQL) kan bij het terugdraaien van een transactie autoincrement "voorbij schieten", waardoor de waarde onveranderd blijft.
SEQUENCE kan in meerdere tabellen worden gebruikt, maar dat bemoeilijkt het behouden van uniciteit.

Strikvraag

Wat gebeurt er als tegelijkertijd twee verzoeken nextval() aanvragen uit één SEQUENCE? Is het mogelijk om niet-unieke waarden te krijgen?

Antwoord: Nee, SEQUENCE garandeert uniciteit. Elke aanvraag ontvangt een unieke waarde, zelfs bij gelijktijdige toegang. De volgorde kan echter niet overeenkomen met de volgorde van invoegen vanwege parallelisme.

Voorbeeld:

-- De eerste thread verkrijgt nextval=100, de tweede na een moment — nextval=101.
-- Zelfs als de eerste later alles terugdraait, is waarde 100 al in gebruik.

Verhaal #1

Na de migratie van de database van MySQL naar PostgreSQL werd de verschillen tussen SEQUENCE en IDENTITY vergeten: handmatig geprobeerd om id's in te voegen, wat leidde tot een fout "duplicate key" bij automatische generatie. Dit werd hersteld door het beheer van sleutels volledig over te dragen aan SEQUENCE.

Verhaal #2

Bij het gedeeltelijk verwijderen van rijen uit een tabel met autoincrement was het ooit nodig om het bereik van id's "nauw" in te vullen. Vanwege ongecontroleerde hercreatie van nieuwe rijen ontstonden ambiguïteiten — sleutels werden inconsistent met de logica van de bedrijfsprocessen, en het was nodig om over te schakelen op een aparte SEQUENCE voor controle.

Verhaal #3

Door gebrek aan controle over SEQUENCE in een groot OLTP-systeem oversteeg het ordernummer 2.147.483.647 en veroorzaakte een noodstop in de integratie API, omdat deze niet voorbereid was op bigint-waarden. Het verzoek werd opnieuw geïmplementeerd met controle op de grenswaarden van SEQUENCE.

Hoe werken en wat zijn de principiële verschillen tussen het genereren en verwerken van sequenties (SEQUENCE/IDENTITY) bij het automatisch creëren van unieke sleutels in SQL? Welke potentiële problemen kunnen zich voordoen?

Antwoord

Strikvraag