Risposta

SEQUENCE (ad esempio, in PostgreSQL e Oracle) — oggetto speciale del database che memorizza il valore corrente e consente di ottenere numeri unici per l'inserimento (di solito — chiavi). IDENTITY — proprietà di un campo che assegna automaticamente il valore successivo in ordine durante l'inserimento (autoincremento delle tabelle in SQL Server, MySQL, PostgreSQL).

Differenze chiave:

SEQUENCE — oggetto esterno alla tabella, accessibile da diversi luoghi, si può ottenere il valore successivo senza inserimento (nextval / currval). Utilizzato per tabelle diverse, possono esserci più SEQUENCE per una tabella.
IDENTITY — parte della definizione della colonna, aumenta automaticamente solo durante l'inserimento di una riga.

Esempio:

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

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id verrà assegnato automaticamente

Problemi e dettagli:

Se si inserisce manualmente l'id, potrebbero verificarsi "vuoti" o sovrapposizioni.
Quando si eliminano righe — i valori non vengono riutilizzati, possibile interruzione nell'intervallo delle chiavi.
In alcune DBMS (ad esempio, MySQL) durante il rollback di una transazione, l'autoincremento può "saltare in avanti", lasciando un valore invariato.
SEQUENCE può essere utilizzato in più tabelle, ma questo complica il mantenimento dell'unicità.

Domanda trabocchetto

Cosa succede se due richieste richiedono contemporaneamente nextval() da una stessa SEQUENCE? È possibile ottenere valori non unici?

Risposta: No, SEQUENCE garantisce unicità. Ogni richiesta ottiene un valore unico, anche con richieste simultanee. Tuttavia, l'ordine potrebbe non corrispondere all'ordine di inserimento a causa del parallelismo.

Esempio:

-- Il primo thread ottiene nextval=100, il secondo dopo un attimo — nextval=101.
-- Anche se poi il primo annulla tutto, il valore 100 è già utilizzato.

Storia n. 1

Dopo la migrazione del database da MySQL a PostgreSQL, sono stati dimenticati i dettagli di SEQUENCE e IDENTITY: hanno provato a inserire manualmente l'id, causando un errore "chiave duplicata" durante la generazione automatica. Hanno corretto, trasferendo completamente la gestione delle chiavi su SEQUENCE.

Storia n. 2

Durante la parziale eliminazione di righe da una tabella con autoincrement, è stato necessario "riempire" strettamente l'intervallo degli id. A causa della creazione incontrollata di nuove righe, sono emerse ambiguità: le chiavi sono diventate incoerenti con la logica aziendale, quindi è stato necessario passare a una SEQUENCE separata per il controllo.

Storia n. 3

A causa della mancanza di controllo su SEQUENCE in un grande sistema OLTP, il numero dell'ordine ha superato 2.147.483.647, causando un guasto all'API di integrazione, poiché non era pronto per valori bigint. La richiesta è stata implementata nuovamente con valori di soglia di controllo per SEQUENCE.

Come funzionano e come si differenziano fondamentalmente generazione e gestione delle sequenze (SEQUENCE/IDENTITY) nella creazione automatica di chiavi uniche in SQL? Quali problemi potenziali possono sorgere?

Risposta

Domanda trabocchetto