In questa pagina viene descritto come gestire le relazioni di chiave esterna nel database.
Una chiave esterna è una colonna condivisa tra tabelle per stabilire un collegamento tra i dati correlati. Quando usi una chiave esterna, Spanner garantisce che questa relazione venga mantenuta.
Il seguente diagramma mostra un semplice schema di database in cui i dati di una tabella hanno una relazione con i dati di un'altra tabella.
Figura 1. Diagramma di uno schema di database di elaborazione degli ordini
Nello schema mostrato nella Figura 1 sono presenti tre tabelle:
- La tabella
Customers
registra i nomi di ciascun cliente. - La tabella
Orders
tiene traccia di tutti gli ordini effettuati. - La tabella
Products
memorizza le informazioni sul prodotto per ogni prodotto.
Tra queste tabelle esistono due relazioni di chiave esterna:
Viene definita una relazione di chiave esterna tra la tabella
Orders
e la tabellaCustomers
per garantire che non sia possibile creare un ordine a meno che non ci sia un cliente corrispondente.Una relazione di chiave esterna tra la tabella
Orders
e la tabellaProducts
assicura che non sia possibile creare un ordine per un prodotto che non esiste.
Utilizzando lo schema precedente come esempio, in questo argomento vengono illustrate le istruzioni CONSTRAINT
DDL (Data
Definition Language) utilizzabili per gestire le relazioni tra le tabelle di un database.
Aggiunta di una chiave esterna durante la creazione di una nuova tabella
Supponi di aver creato una tabella Customers
nel database di ordine dei prodotti semplice. Ora hai bisogno di una tabella Orders
per memorizzare le informazioni sugli
ordini effettuati dai clienti. Per garantire che tutti gli ordini siano validi, non vuoi consentire al sistema di inserire righe nella tabella Orders
, a meno che non sia presente anche una voce corrispondente nella tabella Customers
. Ti serve quindi una chiave esterna
per stabilire una relazione tra le due tabelle. Puoi scegliere di aggiungere una colonna CustomerID
alla nuova tabella e utilizzarla come chiave esterna per creare una relazione con la colonna CustomerID
nella tabella Customers
.
Quando crei una nuova tabella con una chiave esterna, utilizzi REFERENCE
per stabilire una relazione con un'altra tabella. La tabella che contiene l'istruzione REFERENCE
è denominata tabella riferimento. La tabella denominata nell'istruzione REFERENCE
è la tabella riferita. La colonna denominata nell'istruzione REFERENCE
è denominata colonna di riferimenti.
L'esempio seguente mostra come utilizzare l'istruzione DDL CREATE TABLE
per
creare la tabella Orders
con un vincolo di chiave esterna che fa riferimento a
CustomerID
nella tabella Customers
.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
L'istruzione precedente contiene una clausola CONSTRAINT
con le seguenti
caratteristiche:
Utilizzo della sintassi
CONSTRAINT
per assegnare un nome a un vincolo, semplificando così l'eliminazione della tabella con il nome che hai scelto.Il vincolo ha il nome
FK_CustomerOrder
. I nomi dei vincoli hanno come ambito lo schema e devono essere univoci all'interno dello schema.La tabella
Orders
, in base alla quale definisci il vincolo, è la tabella di riferimento. La tabellaCustomers
è la tabella di riferimento.La colonna dei riferimenti nella tabella dei riferimenti è
CustomerID
. Fa riferimento al campoCustomerID
nella tabellaCustomers
. Se un utente tenta di inserire una riga inOrders
con unCustomerID
che non esiste inCustomers
, l'inserimento non riesce.
L'esempio seguente mostra un'istruzione di creazione di tabelle alternativa. Qui, il vincolo della chiave esterna è definito senza nome. Quando utilizzi questa sintassi, Spanner genera un nome per te. Per scoprire i nomi di tutte le chiavi esterne, consulta Visualizzare le proprietà di una relazione di una chiave esterna.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Aggiunta di una chiave esterna a una tabella esistente
Devi inoltre assicurarti che i clienti possano ordinare solo i prodotti esistenti. Innanzitutto, devi rilasciare il vincolo esistente. Quindi, puoi utilizzare ALTER TABLE
per aggiungere un altro vincolo di chiave esterna alla tabella Orders
, come mostrato di seguito:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
La colonna di riferimento in Orders
è ProductID
e fa riferimento alla colonna ProductID
in Prodotti. Anche in questo caso, se Spanner nomina questi vincoli per te, utilizza la seguente sintassi:
ALTER TABLE Orders
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Aggiungere una chiave esterna con un'azione di eliminazione durante la creazione di una nuova tabella
Ricorda l'esempio precedente in cui è presente una tabella Customers
in un database
per gli ordini dei prodotti che richiede una tabella Orders
. Vuoi aggiungere un vincolo di chiave esterna
che faccia riferimento alla tabella Customers
. Tuttavia, devi assicurarti
che quando elimini il record di un cliente in futuro, Spanner
elimini anche tutti gli ordini per quel cliente. In questo caso, vuoi utilizzare l'azione ON DELETE CASCADE
con il vincolo di chiave esterna.
La seguente istruzione DDL CREATE TABLE
per la tabella Orders
include il vincolo di chiave esterna che fa riferimento alla tabella Customers
con un'azione ON DELETE
CASCADE
.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) ON DELETE CASCADE
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);
L'istruzione precedente contiene un vincolo di chiave esterna con una clausola ON DELETE CASCADE
. La colonna CustomerID
è una chiave esterna che fa riferimento al campo CustomerID
nella tabella Customers
. Ciò significa che ogni
valore CustomerID
nella tabella Orders
deve esistere anche nella tabella
Customers
. Se un utente tenta di eliminare una riga dalla tabella Customers
, vengono eliminate nella stessa transazione anche tutte le
righe della tabella Orders
che fanno riferimento al valore CustomerID
eliminato.
Aggiungere una chiave esterna con un'azione di eliminazione a una tabella esistente
Devi inoltre assicurarti che gli ordini vengano creati solo per i prodotti esistenti. Puoi utilizzare ALTER TABLE
per aggiungere un altro vincolo di chiave esterna con l'azione ON DELETE CASCADE
alla tabella degli ordini in questo modo:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
REFERENCES Products (ProductID) ON DELETE CASCADE;
Eliminando una riga dalla tabella Products
vengono eliminate tutte le righe nella tabella Orders
che fanno riferimento al valore ProductID
eliminato.
Query sui dati tra relazioni di chiave esterna
SELECT * FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Products ON Orders.ProductsID = Products.ProductID;
Esempi di come Spanner mantiene l'integrità referenziale
Il motivo principale per l'aggiunta di relazioni di chiave esterna è che Spanner possa mantenere l'integrità referenziale dei tuoi dati. Se modifichi i dati in modo da interrompere un vincolo di chiave esterna, l'aggiornamento non riesce e restituisce un errore.
Considera i dati nella Figura 2. Alcuni clienti hanno ordinato dei prodotti, come mostrato nella tabella degli ordini. Data la presenza delle chiavi esterne, puoi garantire che i dati inseriti nella tabella Orders
abbiano
l'integrità referenziale.
Figura 2. Dati di esempio per nel nostro database degli ordini.
I seguenti esempi mostrano cosa succede quando si cerca di modificare i dati in un modo che potrebbe danneggiare l'integrità referenziale.
Aggiungi una riga alla tabella
Orders
con un valoreCustomerID
che non esiste inCustomers
Cosa succede se provi la seguente modifica, sulla base dei dati di esempio del diagramma precedente?
INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID) VALUES (19, 337876, 4, 447);
In questo caso, il sistema tenterà di inserire una riga in
Orders
con unCustomerID
(447) che non esiste nella tabellaCustomers
. Se il sistema fa questo, l'ordine non è valido nel sistema. Tuttavia, con il vincolo di chiave esterna che hai aggiunto alla tabellaOrders
, la tabella è protetta.INSERT
non riesce con il messaggio seguente, supponendo che il vincolo sia chiamatoFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Tentativo di eliminare una riga dalla tabella
Customers
quando al cliente viene fatto riferimento in un vincolo di chiave esterna.Immagina una situazione in cui un cliente annulla l'iscrizione al tuo negozio online. Vuoi rimuovere il cliente dal tuo backend, quindi prova a eseguire l'operazione riportata di seguito.
DELETE FROM Customers WHERE CustomerID = 721;
In questo esempio, Spanner rileva tramite il vincolo di chiave esterna che esistono ancora record nella tabella
Orders
che fanno riferimento alla riga del cliente che stai tentando di eliminare. In questo caso, viene visualizzato il seguente errore.Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.
Per risolvere il problema, devi prima eliminare tutte le voci di riferimento in
Orders
. Puoi anche definire la chiave esterna con l'azioneON DELETE CASCADE
per consentire a Spanner di gestire l'eliminazione delle voci di riferimento.
Visualizzazione delle proprietà di una relazione di chiave esterna
INFORMATION_SCHEMA di Spanner contiene informazioni sulle chiavi esterne e sui relativi indici di supporto. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere eseguendo una query sullo SCHEMA INFORMAZIONI.
Per saperne di più sugli indici di supporto, consulta Indici di supporto di chiavi esterne.
Quali vincoli sono definiti nel mio database?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
Quali chiavi esterne sono definite nel mio database?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Quali sono indici secondari per le chiavi esterne, noti anche come indici di supporto?
Gli indici di supporto delle chiavi esterne sono gestiti da Spanner , quindi l'esecuzione di query su SPANNER_IS_MANAGED
nella vista INDEXES
restituisce tutti gli indici di supporto.
SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';
Qual è l'azione referenziale definita con il vincolo di chiave esterna?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
Per saperne di più, consulta la sezione Schema delle informazioni.
Rimozione di una relazione di chiave esterna
Il seguente DDL elimina un vincolo di chiave esterna dalla tabella Orders
.
ALTER TABLE Orders
DROP CONSTRAINT FK_CustomerOrder;
Gli indici di supporto della chiave esterna vengono eliminati automaticamente quando viene eliminato il vincolo stesso.
Supporto per relazioni di chiave esterna più complesse
Più colonne
Le chiavi esterne possono fare riferimento a più colonne. L'elenco delle colonne forma una chiave corrispondente alla chiave primaria di una tabella o a un indice di supporto. La tabella di riferimento contiene chiavi esterne della chiave della tabella di riferimento.
Nell'esempio seguente, le definizioni di chiave esterna indicano a Spanner che ogni valore SongName
nella tabella TopHits deve avere un valore corrispondente nella tabella Canzoni e che ogni coppia di valori SingerFirstName
e SingerLastName
deve avere una coppia di valori FirstName
e LastName
corrispondente nella tabella Singers.
GoogleSQL
CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)
) PRIMARY KEY (Rank);
PostgreSQL
CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,
-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),
-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),
PRIMARY KEY (Rank)
);
Riferimenti circolari
A volte le tabelle hanno dipendenze circolari, forse per motivi legacy o
per denormalizzazione. Le chiavi esterne di Spanner consentono riferimenti circolari.
Poiché una tabella di riferimento deve esistere prima che una chiave esterna possa farvi riferimento, una di queste
deve essere aggiunta con un'istruzione ALTER TABLE
. Ecco un esempio
- Crea TableA senza chiave esterna
- Crea TableB con un vincolo di chiave esterna su TableA.
- Utilizza
ALTER TABLE
in TableA per creare un riferimento di chiave esterna a TableB.
Tabelle autoreferenziali
Un tipo speciale di riferimento circolare è una tabella che definisce una chiave esterna che fa riferimento alla stessa tabella. Ad esempio, il seguente snippet mostra una chiave esterna per imporre che anche il ManagerId di un dipendente sia un dipendente.
GoogleSQL
CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);
PostgreSQL
CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);
Passaggi successivi
Scopri di più sul supporto delle chiavi esterne in Spanner.
Scopri di più sullo SCHEMA DI INFORMAZIONI di Spanner.