Questo argomento descrive le chiavi esterne in Spanner e come puoi utilizzarle per e imporre l'integrità referenziale nella soluzione di database.
Panoramica
Le chiavi esterne consentono di definire le relazioni tra le tabelle. Spanner garantisce l'integrità dei dati in queste relazioni.
Immagina di essere lo sviluppatore principale di un'attività di e-commerce. Stai progettando un database per elaborare gli ordini dei clienti. Il database deve archiviare informazioni su ogni ordine, cliente e prodotto. La Figura 1 illustra il database di base la struttura dell'applicazione.
Figura 1. Diagramma di un database di elaborazione degli ordini
Definisci una tabella Customers
per archiviare i dati del cliente, una tabella Orders
per tenere traccia di tutti gli ordini effettuati e una tabella Products
per memorizzare le informazioni
su ogni prodotto che i clienti possono ordinare.
La Figura 1 mostra anche i collegamenti tra le tabelle mappate al seguente relazioni con il mondo:
Un cliente ha effettuato un ordine
È stato effettuato un ordine per un prodotto
Decidi che il tuo database deve applicare le regole seguenti per garantire che gli ordini nel nostro sistema siano validi.
Impossibile creare un ordine per un cliente che non esiste.
Un cliente non può ordinare un prodotto che non hai con te.
Quando applichiamo queste regole, o vincoli, diciamo che stiamo mantenendo integrità referenziale dei nostri dati. Quando un database mantiene l'integrità referenziale, tutti i tentativi di aggiungere dati non validi, che comporterebbero link o link non validi tra i dati non riusciranno. L'integrità referenziale impedisce gli errori degli utenti. Spanner applica l'integrità referenziale tramite chiavi esterne.
Forza l'integrità referenziale con chiavi esterne
Esaminiamo di nuovo l'esempio di elaborazione degli ordini, aggiungendo ulteriori dettagli come mostrato nella Figura 2.
Figura 2. Diagramma dello schema del nostro database con chiavi esterne
La struttura ora mostra i nomi e i tipi di colonna in ogni tabella. Tabella Orders
definisce anche due relazioni di chiave esterna. FK_CustomerOrder
garantisce che tutti
righe in Orders
hanno un valore CustomerID
valido. Chiave esterna FK_ProductOrder
garantisce che tutti i valori ProductID
nella tabella Orders
siano validi. La
la seguente tabella associa questi vincoli alle regole del mondo reale che
in modo forzato.
Nome chiave esterna | Vincolo | Descrizione reale |
---|---|---|
FK_CustomerOrder | Verifica che tutte le righe in Orders abbiano un indirizzo
CustomerID |
Un ordine è stato effettuato da un cliente valido |
FK_ProductOrder | Verifica che tutte le righe in Orders abbiano un indirizzo
ProductID |
È stato effettuato un ordine per un prodotto valido |
Spanner non va a buon fine per qualsiasi transazione che tenta di inserire o aggiornare una riga
la tabella Orders
con un valore CustomerID
o ProductID
non trovato in
Tabelle Customers
e Products
. Non va a buon fine anche le transazioni che tentano di
aggiorna o elimina le righe nelle tabelle Customers
e Products
che potrebbero
invalidano gli ID nella tabella Orders
. Per ulteriori dettagli su come
Spanner convalida i vincoli. Fai riferimento al vincolo di transazione
convalida di seguito.
Definisci le chiavi esterne
Le chiavi esterne vengono create e rimosse dal database Spanner
utilizzando DDL. Le chiavi esterne vengono aggiunte a una nuova tabella con l'istruzione CREATE
TABLE
. Analogamente, puoi aggiungere o rimuovere una chiave esterna
una tabella esistente con l'istruzione ALTER TABLE
. Di seguito è riportato un
esempio di creazione di una nuova tabella con una chiave esterna.
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)
);
Per altri esempi su come creare e gestire le chiavi esterne, consulta Creare e gestire relazioni di chiave esterna.
Di seguito è riportato un elenco delle caratteristiche delle chiavi esterne in Spanner.
La tabella che definisce la chiave esterna è la tabella di riferimento e sono le colonne di riferimento.
La chiave esterna fa riferimento alle colonne riferite della tabella riferita.
Come nell'esempio precedente, puoi assegnare un nome a ogni vincolo di chiave esterna. Se quando non specifichi un nome, Spanner genera un nome automaticamente. La è possibile eseguire query in INFORMATION_SCHEMA di Spanner. I nomi dei vincoli hanno un ambito nello schema, insieme ai nomi delle tabelle e indici, ad esempio, e devono essere univoci all'interno dello schema.
Il numero di colonne di riferimento deve essere lo stesso. L'ordine è significativo. La prima colonna di riferimento si riferisce alla prima colonna di riferimento, la seconda alla seconda ecc.
Una colonna di riferimento e la rispettiva controparte a cui viene fatto riferimento devono essere dello stesso tipo. La devono essere indicizzabili.
Impossibile creare chiavi esterne nelle colonne con Opzione
allow_commit_timestamp=true
.Le colonne di array non sono supportate.
Le colonne JSON non sono supportate.
Una chiave esterna può fare riferimento a colonne della stessa tabella (un "autoriferimento" chiave esterna). Un esempio è una tabella Employee con una colonna ManagerId che fa riferimento alla colonna EmployeeId della tabella.
Le chiavi esterne possono anche formare relazioni circolari tra le tabelle in cui le tabelle si riferiscono l'un l'altra, direttamente o indirettamente. La tabella di riferimento deve esistere prima di creare una chiave esterna, quindi almeno una delle chiavi esterne deve essere aggiunto utilizzando l'istruzione
ALTER TABLE
.Le chiavi a cui viene fatto riferimento devono essere univoche. Spanner utilizza il valore
PRIMARY KEY
di la tabella a cui viene fatto riferimento se le colonne di riferimento della chiave esterna corrispondono colonne di chiave primaria della tabella a cui viene fatto riferimento. Se Spanner non può utilizzare il parametro chiave primaria della tabella a cui viene fatto riferimento, crea unUNIQUE NULL_FILTERED INDEX
le colonne di riferimento.Spanner potrebbe anche essere in grado di usare la chiave primaria del riferimento tabella, sebbene sia meno comune. In caso contrario, Spanner crea
NULL_FILTERED INDEX
nelle colonne di riferimento.Le chiavi esterne non utilizzano gli indici secondari che hai creato. crea i suoi propri indici di supporto. Questi indici di supporto possono essere usati nella valutazione delle query, incluse istruzioni force_index esplicite. Il nome degli indici di supporto sono interrogabili da INFORMATION_SCHEMA di Spanner. Per maggiori informazioni per ulteriori informazioni, consulta Indici di backup.
Azioni chiave esterne
Le azioni delle chiavi esterne ti consentono di controllare cosa succede alla colonna vincolata quando la colonna a cui fa riferimento viene eliminata o aggiornata. Spanner supporta l'uso dell'azione ON DELETE CASCADE. Con la chiave esterna ON l'azione DELETE CASCADE, quando elimini una riga che contiene un elemento esterno a cui viene fatto riferimento , vengono eliminate anche tutte le righe che fanno riferimento a quella chiave nella stessa transazione.
Puoi aggiungere una chiave esterna con
un'azione quando crei il database utilizzando DDL. Utilizza la CREATE TABLE
per aggiungere chiavi esterne con un'azione a una nuova tabella. Analogamente, puoi
utilizzare l'istruzione ALTER TABLE
per aggiungere un'azione chiave esterna a un'azione
o rimuovere un'azione di chiave esterna. Di seguito è riportato un esempio di come
crea una nuova tabella con un'azione di chiave esterna.
GoogleSQL
CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);
CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);
PostgreSQL
CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);
CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);
Di seguito è riportato un elenco delle caratteristiche delle azioni chiave esterna in Spanner.
Le azioni delle chiavi esterne possono essere ON DELETE CASCADE o ON DELETE NO ACTION.
Puoi eseguire una query su INFORMATION_SCHEMA per trovare i vincoli di chiave esterna che un'azione.
L'aggiunta di un'azione di chiave esterna a un vincolo di chiave esterna esistente supportati. Devi aggiungere un nuovo vincolo di chiave esterna con un'azione.
Modifiche allo schema di lunga durata
Aggiunta di una chiave esterna a una tabella esistente o creazione di una nuova tabella con un chiave esterna, può portare a operazioni a lunga esecuzione. Nel caso di una nuova tabella, la tabella non può essere modificata fino al completamento dell'operazione a lunga esecuzione.
Per una nuova tabella con una chiave esterna, Spanner deve eseguire il backfill gli indici a cui si fa riferimento in base alle esigenze di ogni chiave esterna.
Per una tabella esistente con una chiave esterna, Spanner deve eseguire il backfill degli indici di riferimento e di riferimento in base alle esigenze. Inoltre, Spanner convalida i dati esistenti nella tabella per garantire che siano conformi il vincolo di integrità referenziale della chiave esterna. La modifica dello schema non va a buon fine se alcuni dati non sono validi.
L'aggiunta di un'azione di chiave esterna a un vincolo esistente non è supportata. Me ti consigliamo di procedere nel seguente modo:
- Aggiungi un nuovo vincolo con azione.
- Elimina il vincolo precedente senza azione.
In questo modo si evita un problema di Long-running Alter Constraint Operation
. Dopo la creazione
la nuova chiave esterna con l'azione ON DELETE CASCADE, l'effetto netto sia
è ELIMINA CASCATA. L'eliminazione di un vincolo potrebbe comportare l'eliminazione del
indici di supporto di chiave esterna se gli indici non sono utilizzati in un'altra chiave esterna
i vincoli. In seguito, se l'utente aggiunge lo stesso vincolo di chiave esterna con
potrebbe richiedere operazioni a lunga esecuzione, tra cui il backfill degli indici,
convalidare vincoli di indice univoci e convalidare i riferimenti di chiave esterna
i vincoli.
Entrambe le modifiche allo schema riportate sopra potrebbero non riuscire se l'indice di riferimento non può essere
creato a causa di una violazione del vincolo UNIQUE
.
Puoi eseguire query INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE per controllare l'estero lo stato di creazione della chiave.
Convalida del vincolo per una transazione
Spanner convalida i vincoli di chiave esterna mentre è in corso una transazione o quando gli effetti delle scritture vengono resi visibili ai operazioni nella transazione.
Un valore inserito nelle colonne di riferimento viene confrontato con i valori di
la tabella e le colonne di riferimento. Righe con NULL
che fanno riferimento a valori
non sono selezionati, ovvero puoi aggiungerli alla tabella di riferimento.
Spanner convalida tutti i vincoli referenziali di chiave esterna applicabili quando si tenta di aggiornare i dati tramite istruzioni DML o un'API. Tutte in attesa viene eseguito il rollback delle modifiche se alcuni vincoli non sono validi.
La convalida avviene immediatamente dopo ogni istruzione DML. Ad esempio, devi inserire la riga di riferimento prima di inserire le relative righe di riferimento. Se utilizzi un l'API mutation, le mutazioni vengono memorizzate nel buffer fino al commit della transazione. La convalida delle chiavi esterne viene differita fino al commit della transazione. In questo è consentito inserire prima le righe di riferimento.
Ogni transazione viene valutata per verificare le modifiche che interessano la chiave esterna i vincoli. Queste valutazioni potrebbero richiedere richieste aggiuntive al server. Gli indici di supporto richiedono anche tempo di elaborazione aggiuntivo per valutare la transazione modifiche e gestire gli indici. È necessario anche spazio di archiviazione aggiuntivo per ogni indice.
Indici di supporto
Le chiavi esterne non utilizzano indici creati dall'utente. Creano la propria propri indici di supporto.
Spanner può creare fino a due indici di supporto secondari per ogni una per le colonne di riferimento e un secondo per le colonne di riferimento. Tuttavia, una chiave esterna di solito fa riferimento alle chiavi primarie del pertanto il secondo indice nella tabella indicata non è solitamente necessario.
L'indice di supporto per la tabella a cui viene fatto riferimento è UNIQUE NULL_FILTERED
index; la creazione della chiave esterna non va a buon fine se i dati esistenti violano le
vincolo di univocità dell'indice. L'indice di supporto per la tabella di riferimento è
NULL_FILTERED
.
Se due o più chiavi esterne richiedono lo stesso indice di supporto, Spanner creerà un unico indice per tutti. Gli indici di supporto vengono eliminati quando le chiavi esterne che le utilizzano vengono eliminate. Gli utenti non possono modificare o eliminare di backup.
Spanner utilizza lo schema di informazioni di ogni database per archiviare
metadati sugli indici di supporto. Righe all'interno di INFORMATION_SCHEMA.INDEXES
con un valore SPANNER_IS_MANAGED
di true
per descrivere gli indici di supporto.
Al di fuori delle query SQL che richiamano direttamente lo schema delle informazioni, La console Google Cloud non mostra altrimenti informazioni sugli indici di supporto di un database.
Azione a cascata di eliminazione a lunga durata
Quando elimini una riga da una tabella di riferimento, Spanner deve eliminare tutte le righe delle tabelle di riferimento che fanno riferimento alla riga eliminata. Questo può portare in un effetto a cascata, dove una singola operazione di eliminazione può generare migliaia di altre operazioni di eliminazione. Aggiunta di un vincolo di chiave esterna con eliminazione a cascata su una tabella o creare una tabella con vincoli di chiave esterna con eliminazione l'azione a cascata può rallentare le operazioni di eliminazione.
Limite di mutazione superato per l'eliminazione a cascata della chiave esterna
L'eliminazione di un numero elevato di record mediante una procedura di eliminazione a cascata di una chiave esterna impatto sulle prestazioni. Il motivo è che ogni record eliminato attiva l'eliminazione di tutti i record ad esso correlati che utilizzano chiave. Se il numero di mutazioni in una transazione è superiore a 80.000, la transazione non va a buon fine.
Se devi eliminare un numero elevato di record mediante il metodo di eliminazione della chiave esterna a cascata, devi eliminare esplicitamente le righe dalle tabelle figlio prima eliminando la riga dalle tabelle padre. Questo impedisce la transazione non vadano a buon fine a causa del limite di mutazioni.
Confronto di chiavi esterne e interfoliazione delle tabelle
L'interfoliazione delle tabelle di Spanner è una buona scelta per molti in cui la chiave primaria della tabella figlio include i dati e colonne di chiave primaria. La colocation delle righe figlio con le righe padre può migliorare significativamente le prestazioni.
Le chiavi esterne sono una soluzione padre-figlio più generale e rispondono a ulteriori usi d'uso diversi. Non si limitano alle colonne di chiave primaria e le tabelle possono avere più relazioni di chiave esterna, sia come genitore in alcune relazioni che come figlio altri. Tuttavia, una relazione di chiave esterna non implica la colocation delle tabelle nel livello di archiviazione.
Vediamo un esempio, utilizzando lo schema di elaborazione degli ordini di cui abbiamo parlato in precedenza.
in questo argomento. Ricorda che la nostra tabella Orders
è stata definita come segue:
Figura 3. Diagramma dello schema del nostro database con chiavi esterne
Il design nella Figura 3 presenta alcune limitazioni. Ad esempio, ogni ordine può al momento contengono solo un articolo dell'ordine.
Immaginiamo che i nostri clienti ci dicano che vorrebbero poter ordinare più articoli
più di un prodotto per ordine. Possiamo migliorare il nostro design introducendo una
Tabella OrderItems
contenente una voce per ogni prodotto del cliente
ordinato. Possiamo introdurre un'altra chiave esterna per rappresentare il nuovo
relazione tra Orders
e OrderItems
. Tuttavia, sappiamo anche che molte
il tempo necessario per eseguire query sugli ordini e i rispettivi ordini
elementi. La colocation di questi dati migliorerebbe il rendimento, quindi creeremo
relazione padre-figlio utilizzando la funzionalità di interfoliazione delle tabelle di Spanner.
Ecco come definiamo la tabella OrderItems
, con interlacciata con Orders
.
GoogleSQL
CREATE TABLE OrderItems (
OrderID INT64 NOT NULL,
ProductID INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
PostgreSQL
CREATE TABLE OrderItems (
OrderID BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
La Figura 4 è una rappresentazione visiva dello schema aggiornato del database risultante
sull'introduzione di questa nuova tabella, OrderItems
, con interlacciamento con Orders
. Qui puoi
puoi anche vedere la relazione one-to-many tra queste due tabelle.
Figura 4. Aggiunta di una tabella OrderItems con interleaving
In questa configurazione, possiamo avere più voci OrderItems
in ogni ordine
e le voci OrderItems
per ogni ordine sono interlacciate, pertanto sono co-
che si trovano insieme agli ordini. Interfoliazione fisica di Orders
e OrderItems
in
in questo modo puoi migliorare le prestazioni, eseguendo il pre-joining delle tabelle
consentendoti di accedere insieme alle righe correlate riducendo al minimo gli accessi ai dischi. Per
esempio, Spanner può eseguire i join mediante chiave primaria localmente, riducendo al minimo
l'accesso al disco e il traffico di rete.
Se il numero di mutazioni in una transazione è superiore a 80.000, la transazione non va a buon fine. Le eliminazioni a cascata così grandi funzionano bene per le tabelle con un "con interleaving nell'elemento padre" ma non per le tabelle con una chiave esterna relazione tra utenti. Se hai una relazione di chiave esterna e devi eliminare un un numero elevato di righe, devi eliminarle esplicitamente dall'istanza tabelle.
Se hai una tabella utente con una relazione di chiave esterna con un'altra tabella e l'eliminazione di una riga dalla tabella a cui si fa riferimento attiva l'eliminazione di milioni di righe, devi progettare lo schema con un'azione di eliminazione a cascata con "con interleaving nel file padre".
Tabella di confronto
La seguente tabella riassume le differenze tra le chiavi esterne e l'interfoliazione delle tabelle. Puoi usare queste informazioni per decidere qual è la scelta giusta per il tuo progetto.
Tipo di relazione genitore-figlio | Interfoliazione dei tavoli | Chiavi esterne |
---|---|---|
Possono usare chiavi primarie | Sì | Sì |
È possibile utilizzare colonne non a chiave primaria | No | Sì |
Numero di genitori supportati | 0 ... 1 | 0 .. N |
Archivia i dati principali e secondari | Sì | No |
Supporta l'eliminazione a cascata | Sì | Sì |
Modalità di corrispondenza nulla | Viene superato se tutti i valori che fanno riferimento non sono distinti dai valori a cui viene fatto riferimento. I valori null non sono distinti dai valori nulli. I valori null sono diversi dai valori non null. |
Viene inviato se un qualsiasi valore di riferimento è null. Viene superato se tutti i valori di riferimento sono diversi da null e la tabella di riferimento ha una riga con valori uguali a quelli di riferimento. Non riesce se non è stata trovata alcuna riga corrispondente. |
Tempi di applicazione | Per operazione quando si utilizza l'API Mutazione. Per istruzione quando utilizzi DML. |
Per transazione quando si utilizza l'API Mutazione. Per istruzione quando utilizzi DML. |
Possono essere rimossi facilmente | No. L'interfoliazione delle tabelle non può essere rimossa dopo la creazione, a meno che non elimini l'intera tabella figlio. | Sì |
Passaggi successivi
Scopri di più su come creare e gestire relazioni di chiave esterna.
Scopri di più sullo schema delle informazioni.