Chiave primaria SQL: un tutorial tecnico completo

Nei database relazionali, la chiave primaria svolge un ruolo cruciale nel garantire l'unicità e l'integrità dei dati all'interno di una tabella. Una chiave primaria è un vincolo che identifica in modo univoco ogni record in una tabella, fungendo da elemento di base per l'indicizzazione, l'applicazione dell'integrità dell'entità e la creazione di relazioni tra tabelle.

In questo tutorial, ti guiderò in un'esplorazione approfondita delle chiavi primarie in SQL, trattando principi fondamentali, metodi di implementazione e tecniche di ottimizzazione. Vedremo anche errori comuni e buone pratiche, oltre a scenari reali. Se stai ancora imparando SQL, ti consiglio di dare un'occhiata alla nostra completa roadmap SQL e di seguire il nostro percorso di competenze SQL Fundamentals. 

Cos'è una chiave primaria SQL?

Una chiave primaria SQL è una colonna o un insieme di colonne che identifica in modo univoco ogni riga in una tabella. Questo significa che nessuna coppia di righe può avere lo stesso valore di chiave primaria. Una tabella può avere una sola chiave primaria, che può essere composta da una o più colonne.

Le chiavi primarie sono fondamentali per applicare l'integrità dei dati e consentire interrogazioni efficienti. Vediamo alcuni aspetti di base.

Le chiavi primarie sono essenziali per la normalizzazione del database e l'integrità referenziale.

Proprietà chiave delle chiavi primarie

1. Univocità: garantisce che ogni valore nelle colonne della chiave primaria sia unico.
2. Non nullabilità: le colonne della chiave primaria non possono contenere valori NULL.
3. Immutabilità: idealmente, i valori della chiave primaria dovrebbero rimanere invariati per tutta la loro durata per evitare incoerenze nelle tabelle che fanno riferimento.

Vincoli e indicizzazione

Quando si definisce una chiave primaria, il database crea automaticamente un indice univoco per applicare il vincolo. Questo indice accelera le prestazioni delle query, soprattutto per le operazioni di ricerca.

Grazie a questo sistema di indici, le chiavi primarie aiutano il motore del database nell'ottimizzazione dei piani di esecuzione. Il risultato è che le tue query saranno più veloci.

Tipi di dati comuni per le chiavi primarie

Le chiavi primarie possono essere di vari tipi di dati. Ecco alcuni tra i più usati:

• INTEGER o SERIAL: efficienti e più comunemente usati.
• BIGINT: utile per tabelle destinate a contenere miliardi di righe.
• UUID: ideale per sistemi distribuiti ed evitare la prevedibilità sequenziale.
• CHAR/VARCHAR: da usare con cautela, tipicamente in chiavi naturali quando necessario.

Tipi e architettura delle chiavi primarie SQL

Le chiavi primarie possono essere classificate in vari tipi in base alla loro struttura e origine. Alcuni esempi:

• Chiavi naturali: derivate da dati esistenti (ad es., numero di previdenza sociale, email) e facili da comprendere, spesso significative per gli utenti business. Tuttavia, comportano il rischio di aggiornamenti, duplicazioni e potenziali problemi di privacy.
• Chiavi surrogate: chiavi artificiali, come ID generati dal sistema, solitamente implementate con SERIAL, BIGSERIAL o UUID. L'uso di tali chiavi offre stabilità, privacy e manutenzione più semplice.

Confrontandole in base alle caratteristiche, possiamo evidenziarne le differenze nella seguente tabella riassuntiva:

Architettura di chiave primaria composta

Una chiave primaria composta è un modo comune per generare una chiave univoca nelle tabelle. La chiave è tipicamente derivata dai dati presenti in due o più colonne.

Tali chiavi sono comuni nelle tabelle di giunzione o associative in cui la relazione stessa richiede unicità.

• Esempio: una tabella course_enrollments potrebbe usare una chiave composta da student_id e course_id.

Considerazioni:

• Minimalismo: evita colonne non necessarie nella chiave composta.
• Complessità: join e riferimenti di chiave esterna diventano più complessi.
• Dimensione degli indici: gli indici possono diventare grandi e influire sulle prestazioni.

Chiavi aggiuntive usate nei database

Oltre alla chiave primaria, ci sono diverse altre chiavi utilizzabili, come:

• Chiave candidata: un insieme di attributi che può identificare in modo univoco una riga.
• Superchiave: qualsiasi superset di una chiave candidata (include colonne aggiuntive).
• Chiave alternata: una chiave candidata che non è stata scelta come chiave primaria.

Se stai decidendo quale campo dati usare come chiave primaria, puoi considerare i seguenti fattori:

• Frequenza di modifica dei dati: con quale frequenza cambiano i dati?
• Tipo e dimensione della colonna: il tipo di dato è adatto all'indicizzazione?
• Pattern di query attesi: questo campo sarà adatto per le interrogazioni?

Definizione e modifica delle chiavi primarie in SQL

Vediamo ora come dichiarare e gestire le chiavi primarie in PostgreSQL usando i comandi SQL. 

Passeremo in rassegna vari scenari, tra cui creazione di tabelle, modifica di tabelle esistenti e gestione del comportamento dei vincoli.

Sintassi di dichiarazione e modifica

Durante la creazione della tabella

In fase di creazione della tabella, dovresti creare i campi che è probabile trovare. In questo caso, genereremo un ID dipendente composto da interi univoci.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,  -- Automatically generates unique integers
    name TEXT NOT NULL,              -- Basic text field, required
    email TEXT UNIQUE                -- Unique constraint to prevent duplicate emails
);Offerto da

Scopri di più sui comandi SQL nel nostro SQL Basics Cheat Sheet.

Questo esempio mostra come definire una chiave primaria inline usando il pseudo-tipo SERIAL. PostgreSQL tratta SERIAL come una colonna di interi auto-incrementante, ideale per chiavi surrogate. Alla colonna email viene anche assegnato un vincolo UNIQUE, consentendole di fungere da chiave alternata.

Uso di ALTER TABLE

Ora, impostiamo employee_id come chiave primaria.

ALTER TABLE employees ADD PRIMARY KEY (employee_id);Offerto da

Questo comando si usa quando una tabella è già stata creata senza una chiave primaria e vuoi aggiungerla successivamente. Assicurati che la colonna a cui stai aggiungendo la chiave primaria non contenga NULL o valori duplicati, altrimenti il comando fallirà.

Eliminazione della chiave primaria

Ora che abbiamo impostato la chiave primaria, puoi anche eliminare il vincolo di chiave primaria.

ALTER TABLE employees DROP CONSTRAINT employees_pkey;Offerto da

Usa questa istruzione per rimuovere un vincolo di chiave primaria esistente. Il nome del vincolo employees_pkey segue la convenzione di denominazione predefinita di PostgreSQL (<table>_pkey). Questo è utile quando si ridisegna lo schema della tabella o si passa a una diversa colonna come chiave primaria.

In PostgreSQL, il nome predefinito del vincolo per le chiavi primarie è table_name_pkey.

Approfondisci PostgreSQL con il nostro cheat sheet.

Gestione di NULL e collisioni

I valori NULL non sono consentiti nelle colonne della chiave primaria. Anche i valori duplicati causeranno errori di violazione del vincolo

Alcune strategie per gestire questo includono:

• Usare INSERT ... ON CONFLICT per evitare duplicati.
• Usare transazioni per verificare la disponibilità della chiave prima degli insert.

Auto-incremento e strategie di identificatori globali

I tipi SERIAL, BIGSERIAL e IDENTITY di PostgreSQL semplificano la generazione degli ID.

Inoltre, gli UUID forniscono identificatori globali sicuri. La loro natura non sequenziale e imprevedibile aumenta anche la sicurezza, soprattutto nel prevenire attacchi di enumerazione. 

Ecco un'implementazione di come creare e usare un UUID come chiave primaria

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE users (
    user_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    username TEXT NOT NULL
);Offerto da

Chiavi primarie nelle relazioni e miglioramento delle prestazioni

Le chiavi primarie sono essenziali per garantire la coerenza tra tabelle relazionali e per migliorare le prestazioni.

Applicazione dell'integrità dell'entità

L'integrità dell'entità è applicata tramite le chiavi primarie impedendo NULL e duplicati. Avere un identificatore univoco aiuta a prevenire anomalie dei dati durante le operazioni di inserimento/aggiornamento.

Chiavi esterne e integrità referenziale

Le chiavi esterne devono corrispondere a valori di chiave primaria esistenti nella tabella di riferimento. L'integrità referenziale assicura che tutti i riferimenti tra tabelle rimangano validi e previene gli "orphan records", ossia record che fanno riferimento a dati inesistenti.

Le azioni a cascata mantengono la coerenza quando le chiavi primarie di riferimento cambiano. 

Questo può includere funzioni a cascata comuni come:

• ON DELETE CASCADE
• ON UPDATE CASCADE
• SET NULL e SET DEFAULT

Indicizzazione e ottimizzazione delle query

Gli indici univoci sulle chiavi primarie velocizzano le query SELECT e JOIN. Tuttavia, una cattiva progettazione delle chiavi può portare a indici gonfiati e prestazioni peggiori.

Se le tue query sono lente e sospetti che sia dovuto a un'indicizzazione scadente, usa EXPLAIN ANALYZE per ispezionare i piani di esecuzione e valutare l'uso degli indici.

Buone pratiche di progettazione ed errori potenziali

Una buona progettazione delle chiavi favorisce mantenibilità, prestazioni e scalabilità. Vediamo alcune best practice ed errori comuni.

Linee guida per la scelta della chiave

• Scegli tipi di dato brevi e a lunghezza fissa (ad es., INT, UUID).
• Evita chiavi soggette a cambiamento.
• Assicurati che la chiave non incorpori significato business per evitare intrecci logici.
• Usa chiavi surrogate per semplicità e normalizzazione.

Errori comuni di implementazione

• Usare più chiavi primarie (ne è consentita solo una per tabella).
• Scambiare i vincoli univoci per chiavi primarie.
• Non definire chiavi primarie, generando record orfani.

Possibili correzioni:

• Aggiungere le chiavi mancanti tramite ALTER TABLE.
• Normalizzare il design dello schema.
• Usare convenzioni di denominazione coerenti.

Considerazioni sulla scalabilità

Quando crei una soluzione di database che scala, cerca di usare chiavi surrogate per mantenere uniformità su set di dati ampi. Evita chiavi composte nei sistemi distribuiti se non necessario. È inoltre indispensabile monitorare regolarmente la dimensione delle tabelle/indici.

Scenari avanzati di implementazione

Gestire le chiavi primarie in sistemi complessi, come database distribuiti o sistemi legacy, richiede strategie avanzate.

Sistemi di database distribuiti

I database distribuiti sono più insidiosi. Devi assicurarti che la generazione delle chiavi sia priva di conflitti tra i nodi. Per questo, puoi usare UUID e servizi centralizzati di gestione delle chiavi

Considerazioni sul partizionamento:

• Assicurati che le chiavi non creino hotspot.
• Usa chiavi hashate o randomizzate durante lo sharding.

Migrazione di sistemi legacy

Quando esegui una migrazione da un database legacy, identifica innanzitutto gli identificatori univoci esistenti. Poi, introduci gradualmente chiavi surrogate insieme alle chiavi naturali.

Adotta un approccio prudente eseguendo il supporto a doppia chiave prima della transizione completa.

Processo di migrazione:

1. Aggiungi la colonna della chiave surrogata.
2. Popola le chiavi per i record esistenti.
3. Modifica le query dell'applicazione.
4. Elimina le vecchie chiavi naturali dopo la verifica.

Dimostrazione delle chiavi primarie con PostgreSQL

Prima di creare tabelle e inserire dati, ti serve un ambiente PostgreSQL funzionante. Questa sezione inizia con una breve guida all'installazione di PostgreSQL, seguita da istruzioni per definire e usare un dataset di esempio per esercitarti con i concetti di chiave primaria.

Passo 1: configurazione iniziale di PostgreSQL

Se non hai ancora installato PostgreSQL, segui questi passaggi per iniziare:

1. Installa PostgreSQL
• Visita il sito ufficiale: https://www.postgresql.org/download/
• Scegli il tuo sistema operativo (Windows, macOS o Linux).
• Scarica ed esegui l'installer, assicurandoti di installare pgAdmin (un'interfaccia grafica) insieme al server PostgreSQL.
2. Avvia il servizio PostgreSQL
• Dopo l'installazione, il servizio PostgreSQL in genere si avvia automaticamente.
• Puoi anche avviarlo manualmente tramite il gestore dei servizi del tuo sistema operativo (ad es., Servizi su Windows o systemctl su Linux).
3. Avvia pgAdmin o connettiti via CLI
• Usa pgAdmin per gestire e interrogare i database in modo visivo.
• In alternativa, apri un terminale e connettiti usando:psql -U postgres
• Sostituisci postgres con il tuo nome utente PostgreSQL effettivo se diverso.
4. Crea un nuovo database
• In pgAdmin, fai clic con il tasto destro su "Databases" e seleziona "Create > Database".

• Oppure usa il seguente comando SQL:```sql

CREATE DATABASE pk_tutorial;

Connect to the new database in psql with:```bash
\c pk_tutorialOfferto da

Una volta pronto l'ambiente, puoi procedere con il resto della configurazione del dataset come già illustrato nel tutorial.

Passiamo ora a una guida passo passo per creare un dataset di esempio con PostgreSQL. Il dataset è composto da due tabelle correlate: customers e orders.

La tabella customers usa una chiave surrogata per semplicità, mentre la tabella orders usa una chiave primaria basata su UUID per illustrare l'unicità globale.

Passo 2: creazione delle tabelle

Il primo passo è definire lo schema e stabilire i vincoli di chiave primaria per entrambe le tabelle. Definiamo anche una relazione di chiave esterna tra orders.customer_id e customers.customer_id per dimostrare l'integrità referenziale.

Per la tabella customers, la chiave primaria è customer_id.

Per la tabella orders, la chiave primaria è order_id.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Auto-incremented surrogate key
    name TEXT NOT NULL,
    email TEXT UNIQUE -- Ensures no duplicate email addresses
);

CREATE TABLE orders (
    order_id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- Globally unique identifier
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE -- Enforces referential integrity
);Offerto da

Spiegazione:

• customer_id usa la parola chiave SERIAL per generare automaticamente ID univoci.
• email ha un vincolo UNIQUE per prevenire duplicati.
• order_id è un UUID generato automaticamente tramite gen_random_uuid().
• La clausola ON DELETE CASCADE fa sì che eliminare un cliente elimini anche i suoi ordini associati.

Ecco come dovrebbe apparire nell'interfaccia di pgAdmin:

Passo 3: inserimento di dati di esempio

Successivamente, popoliamo le tabelle con record di esempio per osservare come si comportano chiavi primarie ed esterne durante tipiche operazioni di inserimento. Questi dati simulano uno scenario e-commerce di base.

INSERT INTO customers (name, email) VALUES
('Alice Tan', '[email protected]'),
('Ben Koh', '[email protected]'),
('Clara Lim', '[email protected]');

INSERT INTO orders (order_date, customer_id) VALUES
('2025-07-01', 1),
('2025-07-02', 2),
('2025-07-03', 3);Offerto da

Ecco come dovrebbe apparire nell'interfaccia di pgAdmin:

Passo 4: interrogazione dei dati

Infine, eseguiamo una query SQL con JOIN per recuperare informazioni da entrambe le tabelle, dimostrando come la chiave esterna consenta join relazionali senza soluzione di continuità tra ordini e clienti.

Ecco il codice che eseguiremo:

SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Offerto da

Ecco un output di esempio:

Come puoi vedere nell'immagine sopra, la tabella di output mostra dati provenienti da entrambe le tabelle. Ad esempio, la tabella risultante include il campo order_date dalla tabella orders e il campo name dalla tabella customers.

Conclusione

Le chiavi primarie SQL sono fondamentali per i database relazionali: applicano l'unicità, consentono query efficienti e supportano l'integrità dei dati. 

Abbiamo trattato principi, classificazioni, sintassi, buone pratiche e strategie avanzate per le chiavi primarie in SQL. Per ulteriori risorse, dai un'occhiata al nostro corso Introduzione ai database relazionali in SQL o al corso Joining Data in SQL.

Se preferisci letture guidate, la guida ai vincoli di integrità in SQL o il cheat sheet sui join in SQL ti saranno di grande aiuto.