SQL Foreign Key: mantieni sotto controllo le relazioni del tuo database

Chiavi esterne composite e auto-referenziali

Una chiave composita è composta da due o più colonne che identificano univocamente un record e devono essere referenziate insieme. Questo schema si usa quando la chiave naturale copre più campi o se vuoi prevenire relazioni duplicate. Per esempio, potresti avere un'erogazione di corso identificata da (course_id, semester) oppure una tabella di giunzione con student_id e course_id come chiave primaria composita.

-- Defines courses table
CREATE TABLE course_offerings (
    course_id INT,
    semester VARCHAR(10),
    
    -- Composite primary key ensures a course can only be offered once per semester
    PRIMARY KEY (course_id, semester) 
);

-- Create enrollment table
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    semester VARCHAR(10),

    -- This foreign key links enrollment records to a valid course offering
    FOREIGN KEY (course_id, semester)
        REFERENCES course_offerings(course_id, semester)
);Offerto da

Una chiave esterna auto-referenziale o ricorsiva è una chiave esterna che fa riferimento alla chiave primaria all'interno della stessa tabella. Questo schema è comune con dati gerarchici come Dipendente → Manager. Consente di rappresentare relazioni ricorsive senza creare tabelle aggiuntive.

Nella query seguente, manager_id fa riferimento a employee_id nella stessa tabella.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),

    -- manager_id references employee_id in the same table
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);Offerto da

Lavorare con i dati e garantire l'integrità

Ora sappiamo che le chiavi esterne servono a garantire l'integrità dei dati nei database. In questa sezione, vedremo le considerazioni pratiche quando si lavora con chiavi esterne per le operazioni SQL, le sfide comuni e come gestire i vincoli.

Operazioni INSERT, UPDATE e DELETE

Vediamo come queste operazioni interagiscono con le chiavi esterne.

Operazioni di inserimento con chiavi esterne

Per inserire un record figlio, il valore del padre referenziato deve esistere. Se non esiste, l'inserimento fallisce. Per esempio, la query seguente inserirà il record nella tabella orders se la tabella users contiene un record in cui user_id è 1.

-- users table contains user_id = 1
INSERT INTO orders (order_id, user_id, order_date)
VALUES (100, 1, '2024-02-01');Offerto da

Se provi a inserire un record per un utente che non esiste nella tabella padre, otterrai un errore che segnala una violazione del vincolo di chiave esterna.

-- user_id = 999 does NOT exist in users table
INSERT INTO orders (order_id, user_id, order_date)
VALUES (101, 999, '2024-02-01');Offerto da

ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_users"Offerto da

Operazioni di aggiornamento e comportamento in cascata

Gli aggiornamenti possono riguardare sia la tabella della chiave esterna sia quella della chiave primaria. Per esempio, se aggiorni la chiave esterna nella tabella orders, l'operazione avrà successo solo se la chiave primaria esiste nella tabella padre. Per esempio, l'update seguente avrà successo solo se users.user_id = 5 esiste.

-- updating foreign key value
UPDATE orders
SET user_id = 5
WHERE order_id = 100;Offerto da

Dall'esempio sopra, non puoi aggiornare la chiave primaria senza un ON UPDATE CASCADE perché le righe figlio fanno ancora riferimento a 1. Per evitarlo, prima eliminiamo il vincolo di chiave esterna esistente, poi lo ricreiamo con ON UPDATE CASCADE attivo. In questo modo, i valori correlati di orders.user_id si aggiornano automaticamente.

-- Drop existing foreign key constraint
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;

-- Recreate FK with ON UPDATE CASCADE enabled
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE;   -- Automatically update child rows when parent key changesOfferto da

UPDATE users
SET user_id = 500
WHERE user_id = 1;Offerto da

Operazioni di cancellazione e cascate

Se provi a rimuovere una riga padre senza alcuna regola di cascata, la cancellazione fallirà e riceverai un errore. Quando aggiungi una clausola ON DELETE CASCADE, indichi al database di rimuovere automaticamente le righe figlio correlate quando il record padre viene eliminato, mantenendo tutto coerente.

Nell'esempio seguente, creiamo una chiave esterna su orders.user_id che fa riferimento a users.user_id. Con ON DELETE CASCADE, se un utente viene eliminato dalla tabella users, tutti gli ordini correlati verranno eliminati automaticamente.

-- Add a new foreign key constraint to the orders table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE CASCADE;             -- Delete orders automatically when user is deletedOfferto da

A volte potresti voler che il database gestisca automaticamente le cancellazioni. Puoi farlo con ON DELETE SET NULL durante la creazione della tabella. Questo imposterà a NULL le colonne di chiave esterna nella tabella figlia se il record padre cambia o viene eliminato.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,               -- FK referencing users.user_id
    order_date DATE,
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE SET NULL     -- If a user is deleted, set user_id in orders to NULL
);Offerto da

Gestire le sfide comuni

Man mano che progetti database complessi, è probabile che tu incontri problemi con le chiavi esterne. Di seguito trovi i problemi più comuni che ho riscontrato e le soluzioni pratiche per ciascuno.

• Tipi di dato non corrispondenti: Se le colonne della chiave esterna e della chiave primaria referenziata hanno tipi di dato incompatibili, il vincolo non può essere creato. Per evitarlo, standardizza sempre i tipi tra le tabelle correlate.

• Dipendenze circolari: Si verificano quando due tabelle si referenziano a vicenda, potenzialmente causando stalli in inserimento. Per risolvere, rivedi il design dello schema oppure crea la tabella senza chiavi esterne, carica i dati di base e poi usa ALTER TABLE per aggiungere i vincoli.

• Chiavi esterne pendenti: Questo problema si verifica quando la chiave esterna di una figlia punta a un padre inesistente. Per correggerlo, usa azioni in cascata per validare i dati durante le operazioni di delete e update.

Gestire modifiche ed eliminazione dei vincoli

A volte potresti voler modificare i vincoli di chiave esterna durante la riprogettazione dello schema del database. Di seguito i modi per eliminare la chiave esterna, con piccole variazioni di sintassi tra i vari dialetti SQL.

In PostgreSQL, SQL Server e Oracle usiamo l'istruzione DROP CONSTRAINT.

-- Drop foreign key
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;Offerto da

In MySQL, invece, usiamo l'istruzione DROP FOREIGN KEY per rimuovere la chiave esterna.

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_users;Offerto da

Tieni presente che rimuovere un vincolo di chiave esterna elimina l'applicazione dell'integrità referenziale da parte del database. Dopo la rimozione, potresti inserire o aggiornare dati che violano la relazione, con conseguenti problemi o incoerenze nei dati.

Prestazioni e ottimizzazione

Dagli esempi sopra, abbiamo visto come le chiavi esterne garantiscano un design dei dati efficace mantenendo l'integrità. Tuttavia, è importante capire come ottimizzarle per migliorare le prestazioni dei tuoi database. Di seguito alcuni consigli che ho trovato utili quando lavoro con le chiavi esterne:

Indicizzazione e prestazioni delle query

Indicizzando una chiave esterna, il database può convalidare rapidamente le modifiche durante le operazioni di insert, update o delete, individuando le chiavi corrispondenti nella tabella padre. Allo stesso modo, gli indici velocizzano le join tra tabelle figlie e padri consentendo al motore di database di abbinare le righe in base ai valori della chiave esterna.

Come best practice, ti consiglio di indicizzare esplicitamente le colonne della chiave esterna se il DBMS non lo fa automaticamente.

Tecniche di ottimizzazione delle query

Alcuni motori SQL usano la presenza di chiavi esterne per applicare la join elimination, una tecnica che identifica e rimuove le join non necessarie dal piano di esecuzione. Per esempio, se una query accede solo a colonne della tabella figlia ma include una join sulla tabella padre per un filtro garantito dal vincolo di chiave esterna, l'operazione di join può essere ottimizzata ed eliminata.

Puoi sempre visualizzare i piani di esecuzione per vedere come l'ottimizzatore gestisce chiavi esterne e join. Queste ottimizzazioni e la loro efficacia variano tra i motori SQL, come PostgreSQL, MySQL e SQL Server. Capire il comportamento dell'ottimizzatore del tuo database ti aiuta a scrivere query performanti che sfruttano appieno i vincoli di chiave esterna.

Per esempio, puoi usare la seguente query per visualizzare il piano di esecuzione in PostgreSQL:

-- View execution plan
EXPLAIN ANALYZE
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id;Offerto da

Questa tabella riassume come visualizzare il piano di esecuzione nei diversi database:

Ti consiglio di seguire il corso Joining Data in SQL per imparare i diversi tipi di join in SQL e come lavorare con tabelle correlate nel database.

Casi d'uso avanzati e best practice

Oltre all'implementazione di base, puoi usare le chiavi esterne per costruire architetture di database robuste, scalabili e manutenibili. In questa sezione spiegherò gli scenari specializzati e i principi di progettazione di alto livello da considerare quando usi le chiavi esterne.

Relazioni tra database o tra schemi

Sebbene le chiavi esterne siano progettate per applicare l'integrità all'interno di un singolo database o schema, negli ambienti enterprise più complessi spesso sono necessarie relazioni oltre questi confini. La tabella seguente riassume il supporto tra schemi e tra database per i diversi dialetti SQL.

Manutenzione e audit

Man mano che costruisci chiavi esterne, noterai che cambiano ogni volta che cambia la logica di business. Considera i seguenti modi per verificare e mantenere le chiavi esterne, assicurandoti che siano aggiornate:

• Rivedi i vincoli: Effettua audit periodici dei vincoli di chiave esterna per verificare che restino allineati con le regole di business e i cambiamenti di schema.
• Audit regolare degli indici: Verifica sempre che esistano indici su tutte le colonne di chiave esterna per mantenere prestazioni ottimali nelle join e nelle validazioni, soprattutto dopo grandi import di dati o aggiornamenti di sistema.
• Documentazione e visualizzazione: Usa diagrammi Entity-Relationship (ER) o strumenti di visualizzazione dello schema per documentare le relazioni. Questi supporti visivi assicurano che tutti sviluppatori e analisti comprendano le relazioni e le regole di integrità applicate dalle chiavi esterne.

Best practice per un design di database robusto

Per sfruttare al meglio il funzionamento delle chiavi esterne, segui queste linee guida:

• Allinea le chiavi alla logica di business reale: Assicurati che chiavi primarie ed esterne rappresentino effettivamente entità reali e le relazioni tra di esse, invece di fare affidamento su identificatori artificiali o instabili.

• Usa nomi chiari e coerenti: Scegli nomi descrittivi per i vincoli, come fk_orders_user_id, così lo schema è più facile da mantenere, fare debug e condividere.

• Bilancia rigore e flessibilità: I vincoli devono proteggere la qualità dei dati, ma il design dovrebbe comunque consentire casi limite pratici o validazioni gestite a livello applicativo.

• Evita relazioni circolari: Struttura lo schema in modo che le dipendenze di chiave esterna non si chiudano a loop, poiché rendono insert, update e delete inutilmente complessi.

• Ottimizza le prestazioni: Indicizza correttamente le chiavi esterne e applica con attenzione le azioni in cascata, così mantieni l'integrità senza rallentare il sistema.

Conclusione

Come per qualsiasi abilità, padroneggiare le chiavi esterne richiede pratica. Ti invito a sperimentare con il modeling dello schema, a testare le azioni in cascata e a capire come si comportano i vincoli in scenari reali. Col tempo imparerai a bilanciare regole rigorose con la flessibilità necessaria alle tue applicazioni, creando database resilienti e adattabili al variare dei requisiti.

Ora che sai come usare le chiavi esterne nella progettazione del database, ti consiglio di provare il nostro career track Associate Data Engineer in SQL per apprendere le basi del data engineering e del data warehousing. Infine, se vuoi far crescere le tue competenze di gestione database per il big data, ti consiglio il corso Introduction to Data Modeling in Snowflake per approfondire il dimensional modeling.