Chiave esterna - Foreign key
Una chiave esterna è un insieme di attributi in una tabella che fa riferimento alla chiave primaria di un'altra tabella. La chiave esterna collega queste due tabelle. Un altro modo per dirlo: nel contesto dei database relazionali , una chiave esterna è un insieme di attributi soggetti a un certo tipo di vincoli di dipendenza da inclusione, in particolare un vincolo che le tuple costituite dagli attributi della chiave esterna in una relazione , R, devono esistono anche in qualche altra relazione (non necessariamente distinta), S, e inoltre quegli attributi devono essere anche una chiave candidata in S. In parole più semplici, una chiave esterna è un insieme di attributi che fa riferimento a una chiave candidata. Ad esempio, una tabella denominata TEAM può avere un attributo, MEMBER_NAME, che è una chiave esterna che fa riferimento a una chiave candidata, PERSON_NAME, nella tabella PERSON. Poiché MEMBER_NAME è una chiave esterna, qualsiasi valore esistente come nome di un membro in TEAM deve esistere anche come nome di persona nella tabella PERSON; in altre parole, ogni membro di un TEAM è anche una PERSONA.
Riepilogo
La tabella contenente la chiave esterna è denominata tabella figlio e la tabella contenente la chiave candidata è denominata tabella referenziata o padre. Nella modellazione e implementazione relazionale del database, una chiave candidata è un insieme di zero o più attributi, i cui valori sono garantiti come univoci per ogni tupla (riga) in una relazione. Il valore o la combinazione di valori degli attributi chiave candidati per qualsiasi tupla non possono essere duplicati per qualsiasi altra tupla in quella relazione.
Poiché lo scopo della chiave esterna è identificare una particolare riga della tabella di riferimento, è generalmente necessario che la chiave esterna sia uguale alla chiave candidata in una riga della tabella primaria, oppure non abbia alcun valore (il valore NULL ). . Questa regola è chiamata vincolo di integrità referenziale tra le due tabelle. Poiché le violazioni di questi vincoli possono essere la fonte di molti problemi del database, la maggior parte dei sistemi di gestione del database fornisce meccanismi per garantire che ogni chiave esterna non nulla corrisponda a una riga della tabella di riferimento.
Si consideri, ad esempio, un database con due tabelle: una tabella CUSTOMER che include tutti i dati dei clienti e una tabella ORDER che include tutti gli ordini dei clienti. Supponiamo che l'azienda richieda che ogni ordine debba fare riferimento a un singolo cliente. Per riflettere ciò nel database, viene aggiunta una colonna chiave esterna alla tabella ORDER (ad es. CUSTOMERID), che fa riferimento alla chiave primaria di CUSTOMER (ad es. ID). Poiché la chiave primaria di una tabella deve essere univoca e poiché CUSTOMERID contiene solo i valori di quel campo della chiave primaria, possiamo presumere che, quando ha un valore, CUSTOMERID identificherà il particolare cliente che ha effettuato l'ordine. Tuttavia, questo non può più essere assunto se la tabella ORDER non viene aggiornata quando le righe della tabella CUSTOMER vengono eliminate o la colonna ID modificata e lavorare con queste tabelle potrebbe diventare più difficile. Molti database del mondo reale aggirano questo problema "disattivando" anziché eliminare fisicamente le chiavi esterne della tabella principale o mediante programmi di aggiornamento complessi che modificano tutti i riferimenti a una chiave esterna quando è necessaria una modifica.
Le chiavi esterne svolgono un ruolo essenziale nella progettazione del database . Una parte importante della progettazione del database è assicurarsi che le relazioni tra le entità del mondo reale si riflettano nel database tramite riferimenti, utilizzando chiavi esterne per fare riferimento da una tabella all'altra. Un'altra parte importante della progettazione del database è la normalizzazione del database , in cui le tabelle vengono scomposte e le chiavi esterne consentono di ricostruirle.
Più righe nella tabella di riferimento (o figlio) possono fare riferimento alla stessa riga nella tabella di riferimento (o padre). In questo caso, la relazione tra le due tabelle viene chiamata relazione uno a molti tra la tabella di riferimento e la tabella di riferimento.
Inoltre, la tabella figlia e padre può, infatti, essere la stessa tabella, cioè la chiave esterna fa riferimento alla stessa tabella. Tale chiave esterna è nota in SQL:2003 come chiave esterna autoreferenziale o ricorsiva. Nei sistemi di gestione di database, ciò si ottiene spesso collegando un primo e un secondo riferimento alla stessa tabella.
Una tabella può avere più chiavi esterne e ogni chiave esterna può avere una tabella padre diversa. Ogni chiave esterna viene applicata in modo indipendente dal sistema di database . Pertanto, è possibile stabilire relazioni a catena tra le tabelle utilizzando chiavi esterne.
Una chiave esterna è definita come un attributo o un insieme di attributi in una relazione i cui valori corrispondono a una chiave primaria in un'altra relazione. La sintassi per aggiungere tale vincolo a una tabella esistente è definita in SQL:2003 come mostrato di seguito. L'omissione dell'elenco delle colonne nella REFERENCESclausola implica che la chiave esterna farà riferimento alla chiave primaria della tabella di riferimento. Allo stesso modo, le chiavi esterne possono essere definite come parte CREATE TABLEdell'istruzione SQL.
CREATE TABLE child_table (
col1 INTEGER PRIMARY KEY,
col2 CHARACTER VARYING(20),
col3 INTEGER,
col4 INTEGER,
FOREIGN KEY(col3, col4) REFERENCES parent_table(col1, col2) ON DELETE CASCADE
)
Se la chiave esterna è una sola colonna, la colonna può essere contrassegnata come tale utilizzando la seguente sintassi:
CREATE TABLE child_table (
col1 INTEGER PRIMARY KEY,
col2 CHARACTER VARYING(20),
col3 INTEGER,
col4 INTEGER REFERENCES parent_table(col1) ON DELETE CASCADE
)
Le chiavi esterne possono essere definite con un'istruzione di stored procedure .
sp_foreignkey child_table, parent_table, col3, col4
- child_table : il nome della tabella o vista che contiene la chiave esterna da definire.
- parent_table : il nome della tabella o vista che ha la chiave primaria a cui si applica la chiave esterna. La chiave primaria deve essere già definita.
- col3 e col4 : il nome delle colonne che compongono la chiave esterna. La chiave esterna deve avere almeno una colonna e al massimo otto colonne.
Azioni referenziali
Poiché il sistema di gestione del database applica vincoli referenziali, deve garantire l'integrità dei dati se le righe in una tabella di riferimento devono essere eliminate (o aggiornate). Se esistono ancora righe dipendenti nelle tabelle di riferimento, è necessario considerare tali riferimenti. SQL:2003 specifica 5 diverse azioni referenziali che devono aver luogo in tali occorrenze:
CASCATA
Ogni volta che le righe nella tabella padre (di riferimento) vengono eliminate (o aggiornate), verranno eliminate (o aggiornate) anche le rispettive righe della tabella figlio (di riferimento) con una colonna di chiave esterna corrispondente. Questa operazione viene definita eliminazione (o aggiornamento) a cascata.
LIMITARE
Non è possibile aggiornare o eliminare un valore quando esiste una riga in una tabella di riferimento o figlia che fa riferimento al valore nella tabella di riferimento.
Allo stesso modo, una riga non può essere eliminata finché è presente un riferimento ad essa da una tabella di riferimento o figlio.
Per comprendere meglio RESTRICT (e CASCADE), può essere utile notare la seguente differenza, che potrebbe non essere immediatamente chiara. L'azione referenziale CASCADE modifica il "comportamento" della tabella (figlio) stessa in cui viene utilizzata la parola CASCADE. Ad esempio, ON DELETE CASCADE dice effettivamente "Quando la riga di riferimento viene eliminata dall'altra tabella (tabella principale), quindi elimina anche da me ". Tuttavia, l'azione referenziale RESTRICT modifica il "comportamento" della tabella master, non la tabella figlio, sebbene la parola RESTRICT appaia nella tabella figlio e non nella tabella master! Quindi, ON DELETE RESTRICT dice in modo efficace: "Quando qualcuno tenta di eliminare la riga dall'altra tabella (tabella principale), impedisce l'eliminazione da quell'altra tabella (e, naturalmente, non eliminarla anche da me, ma non è questo il punto principale qui)."
RESTRICT non è supportato da Microsoft SQL 2012 e versioni precedenti.
NESSUNA AZIONE
NESSUNA AZIONE e RESTRICT sono molto simili. La differenza principale tra NO ACTION e RESTRICT è che con NO ACTION il controllo dell'integrità referenziale viene eseguito dopo aver provato a modificare la tabella. RESTRICT esegue il controllo prima di tentare di eseguire l' istruzione UPDATE o DELETE . Entrambe le azioni referenziali agiscono allo stesso modo se il controllo dell'integrità referenziale fallisce: l'istruzione UPDATE o DELETE genererà un errore.
In altre parole, quando un'istruzione UPDATE o DELETE viene eseguita sulla tabella referenziata utilizzando l'azione referenziale NO ACTION, il DBMS verifica al termine dell'esecuzione dell'istruzione che nessuna delle relazioni referenziali sia violata. Questo è diverso da RESTRICT, che presuppone all'inizio che l'operazione violerà il vincolo. Utilizzando NO ACTION, i trigger o la semantica dell'istruzione stessa possono produrre uno stato finale in cui nessuna relazione di chiave esterna viene violata nel momento in cui il vincolo viene finalmente verificato, consentendo così il completamento dell'istruzione.
IMPOSTA NULLO, IMPOSTA PREDEFINITO
In generale, l'azione intrapresa dal DBMS per SET NULL o SET DEFAULT è la stessa sia per ON DELETE che ON UPDATE: il valore degli attributi di riferimento interessati viene modificato in NULL per SET NULL e nel valore predefinito specificato per SET DEFAULT .
Trigger
Le azioni referenziali sono generalmente implementate come trigger impliciti (cioè trigger con nomi generati dal sistema, spesso nascosti). Pertanto, sono soggetti alle stesse limitazioni dei trigger definiti dall'utente e potrebbe essere necessario che il loro ordine di esecuzione rispetto ad altri trigger sia considerato; in alcuni casi potrebbe essere necessario sostituire l'azione referenziale con il relativo trigger definito dall'utente equivalente per garantire l'ordine di esecuzione corretto o per aggirare le limitazioni della tabella mutante.
Un'altra importante limitazione appare con l'isolamento della transazione : le tue modifiche a una riga potrebbero non essere in grado di sovrapporsi completamente perché la riga è referenziata da dati che la tua transazione non può "vedere" e quindi non può essere sovrapposta. Un esempio: mentre la tua transazione sta tentando di rinumerare un conto cliente, una transazione simultanea sta tentando di creare una nuova fattura per lo stesso cliente; mentre una regola CASCADE può correggere tutte le righe della fattura che la tua transazione può vedere per mantenerle coerenti con la riga del cliente rinumerata, non raggiungerà un'altra transazione per correggere i dati lì; poiché il database non può garantire dati coerenti quando le due transazioni si impegnano, una di esse sarà costretta a eseguire il rollback (spesso in base all'ordine di arrivo).
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
Esempio
Come primo esempio per illustrare le chiavi esterne, supponiamo che un database dei conti abbia una tabella con le fatture e ogni fattura sia associata a un determinato fornitore. I dettagli del fornitore (come nome e indirizzo) sono conservati in una tabella separata; ad ogni fornitore viene assegnato un 'numero fornitore' per identificarlo. Ogni record di fattura ha un attributo che contiene il numero del fornitore per quella fattura. Quindi, il "numero fornitore" è la chiave primaria nella tabella Fornitori. La chiave esterna nella tabella Fattura punta a quella chiave primaria. Lo schema relazionale è il seguente. Le chiavi primarie sono contrassegnate in grassetto e le chiavi esterne in corsivo.
Supplier (SupplierNumber, Name, Address) Invoice (InvoiceNumber, Text, SupplierNumber)
L' istruzione Data Definition Language corrispondente è la seguente.
CREATE TABLE Supplier (
SupplierNumber INTEGER NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
CONSTRAINT supplier_pk PRIMARY KEY(SupplierNumber),
CONSTRAINT number_value CHECK(SupplierNumber > 0)
)
CREATE TABLE Invoice (
InvoiceNumber INTEGER NOT NULL,
Text VARCHAR(4096),
SupplierNumber INTEGER NOT NULL,
CONSTRAINT invoice_pk PRIMARY KEY(InvoiceNumber),
CONSTRAINT inumber_value CHECK (InvoiceNumber > 0),
CONSTRAINT supplier_fk
FOREIGN KEY(SupplierNumber) REFERENCES Supplier(SupplierNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
)
