In questa pagina viene descritto come gestire le relazioni di chiave esterna nel database.
Una chiave esterna è una colonna condivisa tra tabelle per stabilire collegamento tra dati correlati. Quando usi una chiave esterna, Spanner assicura che questa relazione venga mantenuta.
Il seguente diagramma mostra un semplice schema di database in cui i dati di una tabella hanno un della 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
eCustomers
per garantire che non sia possibile creare un ordine a meno che non sia presente cliente corrispondente.Una relazione di chiave esterna tra la tabella
Orders
e La tabellaProducts
garantisce che non sia possibile creare un ordine per un prodotto che non esiste.
Utilizzando come esempio lo schema precedente, in questo argomento vengono trattati i
Istruzioni CONSTRAINT
Definition Language (DDL) utilizzabili per gestire
relazioni tra le tabelle in un database.
Aggiunta di una chiave esterna durante la creazione di una nuova tabella
Supponi di aver creato una tabella Customers
nel prodotto semplice
un database di ordinamento. Ora hai bisogno di una tabella Orders
per archiviare le informazioni sull'elemento
gli ordini effettuati dai clienti. Per assicurarti che tutti gli ordini siano validi, non vuoi
consenti al sistema di inserire righe nella tabella Orders
, a meno che non sia presente anche una
voce corrispondente nella tabella Customers
. È quindi necessaria una chiave esterna
stabilire una relazione tra le due tabelle. Un'opzione consiste nell'aggiungere
colonna CustomerID
alla nuova tabella e usala come chiave esterna per creare un
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 di riferimento. La
indicata nell'istruzione REFERENCE
è la tabella riferita. La colonna
denominato nell'istruzione REFERENCE
è denominata colonna referencing (riferimento).
L'esempio seguente mostra come utilizzare l'istruzione DDL CREATE TABLE
per
crea la tabella Orders
con un vincolo di chiave esterna che fa riferimento
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
che ha le seguenti caratteristiche:
caratteristiche:
Utilizzo della sintassi
CONSTRAINT
per assegnare un nome a un vincolo, in modo che venga è più facile rilasciare la tabella utilizzando il nome scelto.Il vincolo ha il nome
FK_CustomerOrder
. I nomi dei vincoli hanno un ambito devono essere univoci all'interno dello schema.La tabella
Orders
, in base alla quale definisci il vincolo, è tabella di riferimento. La tabellaCustomers
è la tabella di riferimento.La colonna dei riferimenti nella tabella dei riferimenti è
CustomerID
. it fa riferimento al campoCustomerID
nella tabellaCustomers
. Se qualcuno prova per 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. In questo caso, di chiave esterna è definito senza nome. Quando utilizzi questa sintassi, Spanner genera un nome per te. Per scoprire i nomi di tutte le persone straniere consulta la sezione Visualizzare le proprietà di una chiave esterna di IA generativa.
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 prodotti che
esistono. 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 al ProductID
in Prodotti. Ancora una volta, se ti va bene che Spanner denomina questi
utilizzare 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 in un prodotto è presente una tabella Customers
che richiede una tabella Orders
. Vuoi aggiungere una chiave esterna
vincolo che fa riferimento alla tabella Customers
. Tuttavia, devi assicurarti
quando in futuro eliminerai il record di un cliente, Spanner
elimina anche tutti gli ordini per quel cliente. In questo caso, vuoi utilizzare
Azione ON DELETE CASCADE
con il vincolo di chiave esterna.
La seguente istruzione DDL CREATE TABLE
per la tabella Orders
include il parametro
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 un
ON DELETE CASCADE
. La colonna CustomerID
è una chiave esterna che
fa riferimento al campo CustomerID
nella tabella Customers
. Ciò significa che ogni
Il valore CustomerID
nella tabella Orders
deve esistere anche in Customers
. Se un utente tenta di eliminare una riga dalla tabella Customers
,
le righe nella tabella Orders
che fanno riferimento al valore CustomerID
eliminato sono
eliminati nella stessa transazione.
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 che
esistono. Puoi utilizzare ALTER TABLE
per aggiungere un altro vincolo di chiave esterna con
ON DELETE CASCADE
alla tabella degli ordini come segue:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
REFERENCES Products (ProductID) ON DELETE CASCADE;
L'eliminazione di una riga dalla tabella Products
elimina tutte le righe nella
Tabella Orders
che fa 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 integrità referenziale dell' i 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 prodotti, ad esempio
visualizzata nella tabella degli ordini. A causa delle chiavi esterne presenti
possiamo garantire che i dati inseriti nella tabella Orders
siano
e l'integrità referenziale.
Figura 2. Dati di esempio per nel nostro database degli ordini.
I seguenti esempi mostrano cosa succede quando provi a modificare i dati in un in modo tale da danneggiare l'integrità referenziale.
Aggiungi una riga alla tabella
Orders
con un valoreCustomerID
che non esistono inCustomers
Che cosa succede se provi la seguente modifica, sulla base dei dati campione nel 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 unaCustomerID
(447) che non esiste nella tabellaCustomers
. Se il sistema questa operazione, l'ordine inserito nel sistema non è valido. Tuttavia, con vincolo di chiave esterna che hai aggiunto alla tabellaOrders
, la tabella è protette. L'erroreINSERT
ha esito negativo con il seguente messaggio, a condizione che il vincoloFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Tenta di eliminare una riga dalla tabella
Customers
quando il cliente a cui 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 queste operazioni: operativa.
DELETE FROM Customers WHERE CustomerID = 721;
In questo esempio, Spanner rileva tramite il vincolo di chiave esterna che nella tabella
Orders
siano ancora presenti record che fanno riferimento al cliente riga 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
. Tu puoi anche definire la chiave esterna con l'azioneON DELETE CASCADE
per Spanner gestisce 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 i rispettivi indici di supporto. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere lo 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 le query per
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 il vincolo stesso viene eliminato.
Supporto per relazioni di chiave esterna più complesse
Più colonne
Le chiavi esterne possono fare riferimento a più colonne. L'elenco di colonne forma una chiave corrisponde alla chiave primaria di una tabella o a un indice di supporto. Il riferimento contiene chiavi esterne della chiave della tabella a cui viene fatto riferimento.
Nell'esempio seguente, le definizioni di chiave esterna indicano a Spanner
che ogni valore SongName
nella tabella TopHits deve avere un valore corrispondente in
la tabella Brani; e ogni coppia di valori SingerFirstName
e SingerLastName
devono avere una coppia di valori FirstName
e LastName
corrispondente nella sezione Cantanti
.
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
a causa della denormalizzazione. Le chiavi esterne di Spanner consentono riferimenti circolari.
Poiché una tabella di riferimento deve esistere prima che una chiave esterna possa farvi riferimento, uno dei
le chiavi esterne devono essere aggiunte 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 fa riferimento alla stessa tabella. Ad esempio, il seguente snippet mostra una chiave esterna per far sì 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.