Eliminazione e modifica dei dati considerando le foreign key

Allora, ormai sappiamo già cos’è una foreign key (FOREIGN KEY), come funziona e abbiamo anche fatto pratica creando tabelle che la usano. Ma che succede quando arriva il momento di cancellare dati o modificare record collegati? Ora vediamo proprio come funzionano eliminazione e modifica dei dati insieme alle foreign key, e qualche trucchetto utile.

Pensa al database come a un castello di carte super complicato. Se togli una carta, rischi di far crollare tutto. Ecco perché entrano in gioco le foreign key, che impediscono di “distruggere” il nostro database quando eliminiamo dati collegati. Vediamo come funziona tutto questo.

Cosa succede se provi a eliminare dei dati?

Quando in una tabella c’è una foreign key, il DBMS controlla se il record è collegato ad altre tabelle. Se sì, provare a cancellare il record può causare un errore di integrità dei dati. Per evitare sorprese, puoi impostare in anticipo cosa deve succedere alla foreign key. Queste azioni si configurano con le opzioni ON DELETE.

Configurare il comportamento con ON DELETE

Puoi scegliere una di queste regole per la foreign key quando la crei:

1. ON DELETE CASCADE: Eliminando un record nella tabella padre, vengono automaticamente eliminati tutti i record collegati nella tabella figlia.
2. ON DELETE SET NULL: Invece di eliminare i record collegati, la foreign key viene impostata a NULL.
3. ON DELETE SET DEFAULT: Il valore della foreign key viene impostato al valore di default.
4. ON DELETE RESTRICT (comportamento di default): Non puoi eliminare il record se ci sono record collegati, e viene lanciato un errore.
5. ON DELETE NO ACTION: Praticamente come RESTRICT, ma il controllo di integrità viene rimandato fino alla fine della transazione.

Esempio: Eliminazione a cascata ON DELETE CASCADE

-- Tabella clienti
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabella ordini
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

-- Inserimento dati
INSERT INTO customers (name) VALUES ('Ivan Ivanov');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2023-10-01');

-- Eliminazione cliente
DELETE FROM customers WHERE customer_id = 1;

-- Controlliamo cosa è successo alla tabella orders
SELECT * FROM orders; -- Nessun record, sono stati eliminati a cascata!

Quando eliminiamo un record dalla tabella customers, PostgreSQL elimina automaticamente tutti gli ordini di quel cliente dalla tabella orders.

Esempio: Impostare i riferimenti a NULL ON DELETE SET NULL

-- Tabella ordini con nuova regola di comportamento
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Inserimento dati
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

-- Eliminiamo il cliente
DELETE FROM customers WHERE customer_id = 1;

-- Controlliamo la tabella orders_with_null
SELECT * FROM orders_with_null;

Risultato:

Con ON DELETE SET NULL possiamo tenere gli ordini, ma “scollegarli” dal cliente che ormai non esiste più.

Modifica dei dati considerando le foreign key

Oltre all’eliminazione, anche le modifiche ai dati nelle tabelle padre possono influenzare i record collegati. Per esempio, che succede se un cliente cambia il suo customer_id? Qui entra in gioco l’opzione ON UPDATE.

Configurare il comportamento con ON UPDATE

Puoi gestire le modifiche nella tabella padre con queste strategie:

1. ON UPDATE CASCADE: Cambiando il valore della foreign key nella tabella padre, viene aggiornato automaticamente anche in tutti i record collegati.
2. ON UPDATE SET NULL: Il valore della foreign key nelle tabelle figlie viene impostato a NULL.
3. ON UPDATE SET DEFAULT: Viene impostato il valore di default.
4. ON UPDATE RESTRICT: Non puoi cambiare il valore della foreign key se ci sono record collegati.
5. ON UPDATE NO ACTION: Il controllo viene rimandato fino alla fine della transazione.

Esempio: Aggiornamento a cascata ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Inserimento dati
INSERT INTO customers_with_cascade (name) VALUES ('Ivan Ivanov');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Modifica customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Controlliamo la tabella orders_with_cascade
SELECT * FROM orders_with_cascade;

Risultato:

Quando modifichi customer_id, PostgreSQL lo aggiorna automaticamente anche nella tabella orders_with_cascade.

Esercizio pratico: Tabella enrollments

Riprendiamo il nostro esempio con gli studenti students e i corsi courses. Lavoreremo con la tabella delle iscrizioni ai corsi enrollments e configureremo la gestione dell’eliminazione dei dati.

-- Tabella studenti
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Tabella corsi
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Tabella intermedia delle iscrizioni
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Inserimento dati
INSERT INTO students (name) VALUES ('Aleksey Petrov');
INSERT INTO courses (title) VALUES ('Matematika');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Eliminiamo lo studente
DELETE FROM students WHERE student_id = 1;

-- Controlliamo la tabella enrollments
SELECT * FROM enrollments; -- Vuoto! Il record è stato eliminato automaticamente.

Errori tipici e come evitarli

Uno degli errori più comuni è provare a eliminare un record dalla tabella padre senza aver configurato correttamente il comportamento delle foreign key. Per esempio, se non hai specificato nulla per ON DELETE, di default viene applicato RESTRICT, che porta a un errore.

Ricorda anche che usare troppo spesso le operazioni a cascata (CASCADE) può avere effetti imprevisti. Per esempio, potresti cancellare più dati di quanto pensavi.

Per evitare questi problemi, segui questi consigli:

• Pensa sempre bene a come impostare ON DELETE e ON UPDATE, in base alla logica della tua applicazione.
• Per le operazioni più delicate, aggiungi query di controllo prima di fare modifiche o cancellazioni.
• Usa le transazioni, così puoi fare rollback se succede un errore.