Chiavi esterne

Questa pagina descrive le chiavi esterne in Spanner e come utilizzarle per applicare l'integrità referenziale nel database.

Le chiavi esterne definiscono le relazioni tra le tabelle. Spanner garantisce la conservazione dell'integrità dei dati di queste relazioni.

Immagina di essere uno sviluppatore principale per un'attività di e-commerce. Stai progettando un database per elaborare gli ordini dei clienti. Il database deve memorizzare informazioni su ogni ordine, cliente e prodotto. La Figura 1 mostra la struttura di base del database per l'applicazione.

Figura 1. Diagramma di un database di elaborazione degli ordini

Definisci una tabella Customers per archiviare le informazioni sui clienti, una tabella Orders per monitorare tutti gli ordini e una tabella Products per archiviare le informazioni su ciascun prodotto.

La Figura 1 mostra anche i link tra le tabelle che mappano ai seguenti relazioni reali:

• Un cliente effettua un ordine.

• Viene effettuato un ordine per un prodotto.

Decidi che il tuo database applichi le seguenti regole per garantire che gli ordini nel tuo sistema siano validi.

• Non puoi creare un ordine per un cliente che non esiste.

• Un cliente non può effettuare un ordine per un prodotto che non vendi.

Quando applichi queste regole o limitazioni, mantieni la integrità referenziale dei tuoi dati. Quando un database mantiene l'integrità referenziale, tutti i tentativi di aggiungere dati non validi, che comporterebbero link o riferimenti non validi tra i dati, non riescono. L'integrità referenziale impedisce errori da parte dell'utente. Spanner applica l'integrità referenziale tramite le chiavi esterne.

Applicare l'integrità referenziale con le chiavi esterne

Di seguito viene esaminato di nuovo l'esempio di elaborazione degli ordini, con ulteriori dettagli aggiunti al design, come mostrato nella Figura 2.

Figura 2. Diagramma di uno schema di database con chiavi esterne

Il design ora mostra i nomi e i tipi di colonna in ogni tabella. La tabella Orders definisce anche due relazioni con chiave esterna. FK_CustomerOrder garantisce che tutte le righe di Orders abbiano un CustomerID valido. La chiave esterna FK_ProductOrder garantisce che tutti i valori ProductID nella tabella Orders siano validi. La tabella seguente mappa questi vincoli alle regole del mondo reale che vuoi applicare.

Nome chiave esterna	Vincolo	Descrizione del mondo reale
FK_CustomerOrder	Garantisce che tutte le righe in Orders abbiano un valore valido per CustomerID	Un cliente valido effettua un ordine
FK_ProductOrder	Garantisce che tutte le righe in Orders abbiano un valore valido per ProductID	È stato effettuato un ordine per un prodotto valido

Spanner non riesce a completare qualsiasi transazione che tenta di inserire o aggiornare una riga nella tabella Orders contenente un valore CustomerID o ProductID non trovato nelle tabelle Customers e Products. Inoltre, non riesce le transazioni che tentano di aggiornare o eliminare righe nelle tabelle Customers e Products che invaliderebbero gli ID nella tabella Orders. Per ulteriori dettagli su come Spanner convalida i vincoli, consulta la sezione Convalida dei vincoli delle transazioni.

Caratteristiche delle chiavi esterne

Di seguito è riportato un elenco di caratteristiche delle chiavi esterne in Spanner.

• La tabella che definisce la chiave esterna è la tabella di riferimento e le colonne della chiave esterna sono le colonne di riferimento.

• La chiave esterna fa riferimento alle colonne di riferimento della tabella di riferimento.

• Come nell'esempio, puoi assegnare un nome a ogni vincolo di chiave esterna. Se non specifichi un nome, Spanner ne genera uno per te. Puoi eseguire query sul nome generato da INFORMATION_SCHEMA di Spanner. I nomi delle limitazioni hanno come ambito lo schema, insieme ai nomi delle tabelle e degli indici, e devono essere univoci all'interno dello schema.

• Il numero di colonne di riferimento e di riferimento deve essere lo stesso. L'ordine è importante. La prima colonna di riferimento fa riferimento alla prima colonna di riferimento, la seconda alla seconda e così via.

• Una colonna di riferimento e la relativa controparte a cui fa riferimento devono essere dello stesso tipo. Devi essere in grado di indicizzare le colonne.

• Non puoi creare chiavi esterne nelle colonne con l'opzione allow_commit_timestamp=true.

• Le colonne di tipo array non sono supportate.

• Le colonne JSON non sono supportate.

• Una chiave esterna può fare riferimento alle colonne della stessa tabella (una chiave esterna autoreferenziale). 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 tabelle in cui due tabelle fanno riferimento l'una all'altra, direttamente o indirettamente. La tabella a cui si fa riferimento deve esistere prima di creare una chiave esterna. Ciò significa che almeno una delle chiavi estranee deve essere aggiunta utilizzando l'istruzione ALTER TABLE.

• Le chiavi a cui si fa riferimento devono essere univoche. Spanner utilizza il PRIMARY KEY della tabella a cui si fa riferimento se le colonne a cui fa riferimento la chiave esterna corrispondono alle colonne della chiave primaria della tabella a cui si fa riferimento. Se Spanner non può utilizzare la chiave primaria della tabella a cui si fa riferimento, crea un UNIQUE NULL_FILTERED INDEX sulle colonne a cui si fa riferimento.

• Spanner può anche utilizzare la chiave primaria della tabella di riferimento, anche se questa operazione è meno comune. In caso contrario, Spanner crea un NULL_FILTERED INDEX sulle colonne di riferimento.

• Le chiavi esterne non utilizzano gli indici secondari che hai creato. ma creano i propri indici di supporto. Gli indici di supporto sono utilizzabili nelle valutazioni delle query, anche nelle direttive force_index esplicite. Puoi eseguire query sui nomi degli indici di supporto da INFORMATION_SCHEMA di Spanner. Per ulteriori informazioni, consulta la sezione Indici di supporto.

Definisci le chiavi esterne

Puoi creare e rimuovere le chiavi esterne dal database Spanner utilizzando DDL. Aggiungi le chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere una chiave esterna a una tabella esistente o rimuoverla 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 con le chiave esterna esterne.

Nota: puoi anche aggiungere e rimuovere le chiavi esterne utilizzando la console Google Cloud . Seleziona Modifica come testo dall'interfaccia di creazione della tabella e inserisci il DDL per gestire le chiavi esterne della tabella.

Azioni delle chiavi esterne

Le azioni delle chiavi esterne controllano 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 l'azione ON DELETE CASCADE della chiave esterna, quando elimini una riga contenente una chiave esterna 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 l'istruzione CREATE TABLE per aggiungere chiavi esterne con un'azione a una nuova tabella. Analogamente, puoi utilizzare l'istruzione ALTER TABLE per aggiungere un'azione di chiave esterna a una tabella esistente o per rimuoverla. Di seguito è riportato un esempio di come creare 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 delle chiave esterna in Spanner.

• Le azioni delle chiavi esterne sono ON DELETE CASCADE o ON DELETE NO ACTION.

• Puoi eseguire query su INFORMATION_SCHEMA per trovare le limitazioni delle chiave esterna che hanno un'azione.

• L'aggiunta di un'azione di chiave esterna a una limitazione di chiave esterna esistente non è supportata. Devi aggiungere una nuova limitazione della chiave esterna con un'azione.

Modifiche allo schema a lungo termine

L'aggiunta di una chiave esterna a una tabella esistente o la creazione di una nuova tabella con una chiave esterna può comportare operazioni che richiedono molto tempo. Nel caso di una nuova tabella, la tabella non è scrivibile finché l'operazione a lunga esecuzione non è completata.

Per una nuova tabella con una chiave esterna, Spanner deve eseguire il backfill degli indici di riferimento in base alle esigenze per ogni chiave esterna.

Per una tabella esistente con una chiave esterna, Spanner deve eseguire il completamento dei dati degli indici di riferimento e a cui si fa riferimento, se necessario. Inoltre, Spanner convalida i dati esistenti nella tabella per assicurarsi che rispettano il vincolo di integrità referenziale della chiave esterna. La modifica dello schema non va a buon fine se i dati non sono validi.

L'aggiunta di un'azione di chiave esterna a una limitazione esistente non è supportata. Ecco cosa devi fare:

1. Aggiungi una nuova limitazione con l'azione richiesta.
2. Rimuovi la limitazione precedente che non ha l'azione.

In questo modo si evita un problema di Long-running Alter Constraint Operation. Dopo aver creato la nuova chiave esterna con l'azione ON DELETE CASCADE, l'effetto netto di entrambi i vincoli è DELETE CASCADE. L'eliminazione di una limitazione potrebbe comportare l'eliminazione degli indici di supporto delle chiave esterna se questi non sono in uso in altre limitazioni delle chiavi esterne. In seguito, se l'utente aggiunge lo stesso vincolo di chiave esterna con azione, potrebbero essere necessarie operazioni di lunga durata, come il backfilling degli indici, la convalida dei vincoli di indice univoco e la convalida dei vincoli di referenza delle chiavi esterne.

Nota: se non elimini la precedente limitazione della chiave esterna, si verifica un overhead dovuto alla convalida delle limitazioni della chiave esterna aggiuntive.

Una delle modifiche dello schema precedenti può non riuscire se non è possibile creare l'indice a cui si fa riferimento a causa di una violazione del vincolo UNIQUE.

Puoi eseguire query su INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE per controllare lo stato di creazione della chiave esterna.

Convalida dei vincoli

Spanner convalida le limitazioni delle chiave esterna al momento del commit di una transazione o quando gli effetti delle scritture vengono resi visibili alle operazioni successive nella transazione.

Un valore inserito nelle colonne di riferimento viene abbinato ai valori della tabella e delle colonne di riferimento. Le righe con valori di riferimento NULL non sono selezionate, il che significa che puoi aggiungerle alla tabella di riferimento.

Spanner convalida tutte le limitazioni referenziali delle chiave esterna applicabili quando si tenta di aggiornare i dati utilizzando istruzioni DML o un'API. Tutte le modifiche in sospeso vengono annullate se i vincoli non sono validi.

La convalida viene eseguita immediatamente dopo ogni istruzione DML. Ad esempio, devi inserire la riga a cui si fa riferimento prima di inserire le righe che fanno riferimento a questa. Quando si utilizza un'API di mutazione, le mutazioni vengono messe in coda fino al commit della transazione. La convalida della chiave estranea viene posticipata fino al commit della transazione. In questo caso, è consentito inserire prima le righe di riferimento.

Per ogni transazione vengono valutate le modifiche che influiscono sulle limitazioni delle chiave esterna. Queste valutazioni potrebbero richiedere richieste aggiuntive al server. Inoltre, gli indici di supporto richiedono un tempo di elaborazione aggiuntivo per valutare le modifiche delle transazioni e per gestire gli indici. È necessario anche uno spazio di archiviazione aggiuntivo per ogni indice.

Indici di supporto

Le chiavi esterne non utilizzano gli indici creati dall'utente. Creano i propri indici di supporto.

Spanner può creare fino a due indici di supporto secondari per ogni chiave esterna, uno per le colonne di riferimento e un altro per le colonne a cui si fa riferimento. Tuttavia, una chiave esterna fa solitamente riferimento alle chiavi primarie della tabella a cui fa riferimento, pertanto il secondo indice della tabella a cui fa riferimento in genere non è necessario.

L'indice di supporto per la tabella a cui viene fatto riferimento è un indice UNIQUE NULL_FILTERED. La creazione della chiave esterna non va a buon fine se i dati esistenti violano il vincolo di unicità 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 crea un singolo indice per tutte. Gli indici di supporto vengono eliminati quando vengono eliminate le chiavi esterne che li utilizzano. Gli utenti non possono alterare o eliminare gli indici di supporto.

Spanner utilizza lo schema delle informazioni di ogni database per archiviare i metadati relativi agli indici di supporto. Le righe all'interno di INFORMATION_SCHEMA.INDEXES che hanno un valore SPANNER_IS_MANAGED pari a true descrivono gli indici di supporto.

A parte le query SQL che invocano direttamente lo schema di informazioni, la console Google Cloud non mostra alcuna informazione sugli indici di base di un database.

Azione di eliminazione a cascata a lungo termine

Quando elimini una riga da una tabella a cui viene fatto riferimento, Spanner deve eliminare tutte le righe nelle tabelle di riferimento che fanno riferimento alla riga eliminata. Ciò può portare a un effetto a cascata, in cui una singola operazione di eliminazione genera migliaia di altre operazioni di eliminazione. L'aggiunta di una limitazione della chiave esterna con un'azione di eliminazione a cascata a una tabella o la creazione di una tabella con limitazioni della chiave esterna con un'azione di eliminazione a cascata può rallentare le operazioni di eliminazione.

Limite di mutazioni superato per l'eliminazione a cascata delle chiave esterna

L'eliminazione di un numero elevato di record utilizzando un'eliminazione a cascata di chiave esterna può influire sulle prestazioni. Questo accade perché ogni record eliminato comporta l'eliminazione di tutti i record correlati. Se devi eliminare un numero elevato di record utilizzando un'eliminazione con eliminazione cascade della chiave esterna, devi eliminare esplicitamente le righe dalle tabelle figlio prima di eliminare la riga dalle tabelle principali. In questo modo, la transazione non fallisce a causa del limite di mutazioni.

Confronto tra chiavi esterne e interfoliazione delle tabelle

L'interleaving delle tabelle di Spanner è una buona scelta per molte relazioni padre-figlio in cui la chiave primaria della tabella figlio include le colonne di chiave primaria della tabella padre. La collocazione di righe figlio con le rispettive righe padre può offrire un miglioramento significativo delle prestazioni.

Le chiavi esterne sono una soluzione padre-figlio più generica e sono adatte a ulteriori casi d'uso. Non sono limitate alle colonne di chiave primaria e le tabelle possono avere più relazioni di chiave esterna, sia come tabella principale in alcune relazioni sia come tabella figlio in altre. Tuttavia, una relazione di chiave esterna non implica la co-location delle tabelle nel livello di archiviazione.

Prendi in considerazione un esempio che utilizza una tabella Orders 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ò contenere un solo articolo.

Immagina che i tuoi clienti vogliano poter ordinare più di un prodotto per ordine. Puoi migliorare il tuo design introducendo una tabella OrderItems che contiene una voce per ogni prodotto ordinato dal cliente. Puoi introdurre un'altra chiave esterna per rappresentare questa nuova relazione one-to-many tra Orders e OrderItems. Tuttavia, sai anche che spesso vuoi eseguire query sugli ordini e sui relativi articoli. Poiché la co-localizzazione di questi dati migliora le prestazioni, ti consigliamo di creare la relazione padre-figlio utilizzando la funzionalità di interlacciamento delle tabelle di Spanner.

Ecco come definire la tabella OrderItems, 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 del database aggiornato a seguito dell'introduzione di questa nuova tabella, OrderItems, interlacciata con Orders. Qui puoi anche vedere la relazione one-to-many tra le due tabelle.

Figura 4. Aggiunta di una tabella OrderItems con interfoliazione

In questa configurazione, puoi avere più voci OrderItems in ogni ordine e le voci OrderItems per ogni ordine sono interlacciate e quindi co-localizzate con gli ordini. Interlacciare fisicamente Orders e OrderItems in questo modo può migliorare le prestazioni, pre-unire efficacemente le tabelle e consentire di accedere contemporaneamente alle righe correlate riducendo al minimo gli accessi al disco. Ad esempio, Spanner può eseguire join per chiave primaria localmente, riducendo al minimo l'accesso al disco e il traffico di rete.

Se il numero di mutazioni in una transazione supera 80.000, la transazione non va a buon fine. Queste eliminazioni concatenate di grandi dimensioni funzionano bene per le tabelle con una relazione "interleaved in parent", ma non per le tabelle con una relazione di chiave esterna. Se hai una relazione con una chiave esterna e devi eliminare un numero elevato di righe, devi prima eliminare esplicitamente le righe dalle tabelle figlio.

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 "interleaved in parent".

Nota: Google consiglia di scegliere di rappresentare le relazioni padre-figlio come tabelle con interleaving o come chiavi esterne, ma non entrambe. L'utilizzo di entrambi è abbondante e potrebbe consumare risorse di calcolo e di archiviazione aggiuntive per verificare i vincoli e gestire gli indici di supporto.

Tabella di confronto

La seguente tabella riassume il confronto tra le chiavi esterne e l'interlacciamento delle tabelle. Puoi utilizzare queste informazioni per decidere cosa è giusto per il tuo design.

Tipo di relazione principale-secondario	Interfoliazione delle tabelle	Chiavi esterne
Può utilizzare chiavi principali	Sì	Sì
Può utilizzare colonne diverse dalla chiave primaria	No	Sì
Numero di genitori supportati	0 .. 1	0 .. N
Memorizza i dati principali e secondari insieme	Sì	No
Supporta l'eliminazione in cascata	Sì	Sì
Modalità di corrispondenza con valori null	Passa se tutti i valori di riferimento non sono distinti dai valori a cui fanno riferimento. I valori null non sono distinti dai valori null; i valori null sono distinti dai valori non null.	Il controllo passa se uno o più valori di riferimento sono null. Il controllo passa se tutti i valori di riferimento sono diversi da null e la tabella a cui si fa riferimento ha una riga con valori uguali a quelli di riferimento. Il controllo non va a buon fine se non viene trovata una riga corrispondente.
Tempi di applicazione	Per operazione quando si utilizza l'API di mutazione. Per istruzione quando si utilizza DML.	Per transazione quando si utilizza l'API di mutazione. Per istruzione quando si utilizza DML.
Può essere rimosso	No. L'interlacciamento delle tabelle non può essere rimosso dopo la creazione, a meno che non venga eliminata l'intera tabella secondaria.	Sì

Passaggi successivi

• Scopri di più sulla creazione e gestione delle chiave esterna esterne.

• Scopri di più sullo schema di informazioni.