Howto Come forzare l'uso delle FK con Sqlite

Da PtLUG Wiki.

Sqlite fornisce la sintassi per la creazione di Foreign Key.

Tuttavia allo stato attuale non esegue questa restrizione risultanto pertanto possibile inserire dati non validi all'interno delle tabelle che dovrebbero, invece, essere vincolate.

Per raggirare questo limitazione è possibile far uso dei trigger.

1 Creazione delle tabelle
2 Inserimento dei dati
3 FK sfruttando i trigger
4 Autore

[edit]

Creazione delle tabelle

Andiamo a fare un piccolo esempio che ci sarà utile per mostrare il nostro espediente: Immaginiamo di dover immagazzinare gli articoli delle nostre riviste preferite e di volere utilizzare solamente due tabelle:

RIVISTA: contiene l'ID e il nome della rivista.
ARTICOLO: contiene oltre al suo id l'id della rivista alla quale l'articolo appartiene, il titolo e la pagina in cui l'articolo inizia.

CREATE TABLE ARTICOLO (
   ID INTEGER NOT NULL PRIMARY KEY,
   ID_RIVISTA INTEGER NOT NULL CONSTRAINT fk_rivista_id REFERENCES RIVISTA(ID) ON DELETE CASCADE,
   TITOLO VARCHAR(200) NOT NULL,
   PAG INTEGER NOT NULL,
   UNIQUE(ID_RIVISTA,TITOLO,PAG)
);

CREATE TABLE RIVISTA (
   ID INTEGER NOT NULL PRIMARY KEY,
   NOME VARCHAR(50) NOT NULL,
   unique(NOME)
);

[edit]

Inserimento dei dati

Iniziamo a popolare la tabella Rivista:

insert into RIVISTA values (null,'Linux & C.');
insert into RIVISTA values (null,'Linu Magazine');

Abbiamo immesso NULL al posto di ID per far sì che sia Sqlite ad occuparsi, in automatico, di aggiornare tale valore (si tratta infatti di un campo di PK).

La tabella RIVISTA sarà adesso così costituita:

ID  NOME          
--  --------------
1   Linux & C.    
2   Linux Magazine

Bene andiamo adesso a popolare la tabella degli articoli:

insert into ARTICOLO values (null,1,'Articolo numero uno',30);
insert into ARTICOLO values (null,1,'Articolo numero due',40);
insert into ARTICOLO values (null,2,'Articolo numero tre',10);
insert into ARTICOLO values (null,2,'Articolo numero quattro',11);

Proviamo a mettere adesso un id_rivista non esistente:

insert into ARTICOLO values (null,3,'Articolo numero cinque',65);

...Caspita!!! Nessun errore. Sqlite ci ha inserito un articolo senza avere la corrispondente rivista. E questo non va bene, abbiamo infatti volutamente violato il vincolo della Foreign Key. Sqlite, infatti, consente di definire le FK ma non ci obbliga a rispettarlo. In sostanza è come se non ci fosse.

Sappiamo tuttavia che in Sqlite è possibile scrivere dei trigger... perchè non usarli per forzare noi il rispetto di queste regole sul nostro database? Innanzitutto rimuoviamo il record che viola la FK:

delete from articolo where id=5;

[edit]

FK sfruttando i trigger

Quindi procediamo a scrivere le nostre poche righe di istruzione per i trigger:

-- CREAZIONE TRIGGER DELETE SULLA TABELLA RIVISTA
-- a cascata andiamo ad eliminare tutti i record della
-- tabella Articolo correlati
CREATE TRIGGER fkdc_ARTICOLO_ID_RIVISTA_RIVISTA_ID
BEFORE DELETE ON RIVISTA
FOR EACH ROW BEGIN
   DELETE FROM ARTICOLO WHERE ARTICOLO.ID_RIVISTA = OLD.ID;
END;

-- CREAZIONE TRIGGER INSERT SULLA TABELLA ARTICOLO
-- Prima dell'inserimento di un articolo andiamo a controllare
-- se esiste la rivista collegata altrimenti alziamo un eccezione
CREATE TRIGGER fki_ARTICOLO_ID_RIVISTA_RIVISTA_ID
BEFORE INSERT ON [ARTICOLO]
FOR EACH ROW BEGIN
 SELECT RAISE(ROLLBACK, 'insert on table "ARTICOLO" violates foreign key constraint "fki_ARTICOLO_ID_RIVISTA_RIVISTA_ID"')
 WHERE (SELECT ID FROM RIVISTA WHERE ID = NEW.ID_RIVISTA) IS NULL;
END;

-- CREAZIONE TRIGGER UPDATE SULLA TABELLA ARTICOLO
-- Prima dell'aggiornamento di un articolo andiamo a controllare
-- se esiste la rivista collegata altrimenti alziamo un eccezione
CREATE TRIGGER fku_ARTICOLO_ID_RIVISTA_RIVISTA_ID
BEFORE UPDATE ON [ARTICOLO]
FOR EACH ROW BEGIN
   SELECT RAISE(ROLLBACK, 'update on table "ARTICOLO" violates foreign key constraint "fku_ARTICOLO_ID_RIVISTA_RIVISTA_ID"')
     WHERE (SELECT ID FROM RIVISTA WHERE ID = NEW.ID_RIVISTA) IS NULL;
END;

Proviamo adesso nuovamente ad inserire un articolo che viola la FK:

insert into ARTICOLO values (null,3,'Articolo numero cinque',65);

Adesso compare un messaggio di errore:

SQL error: insert on table "ARTICOLO" violates foreign key constraint "fki_ARTICOLO_ID_RIVISTA_RIVISTA_ID"

Stessa cosa se proviamo a modificare l'id_rivista di qualche record già presente:

update ARTICOLO set id_rivista=3;
SQL error: update on table "ARTICOLO" violates foreign key constraint "fku_ARTICOLO_ID_RIVISTA_RIVISTA_ID"

Se adesso cancelliamo una rivista, dovremmo cancellare a cascata anche gli articoli correlati:

delete from rivista where id=2;

Nessun errore. Vediamo adesso cosa è rimasto nella tabella degli articoli:

ID  ID_RIVISTA  TITOLO               PAG    
--  ----------  -------------------  ---
1   1           Articolo numero uno  30 
2   1           Articolo numero due  40

Bene abbiamo ovviato a questo fastidioso problemuccio, in attesa che questa regola venga fatta valere direttamente da Sqlite.

[edit]