Chiavi esterne

Questo documento descrive le chiavi esterne in Spanner e come utilizzarle per applicare l'integrità referenziale nel database. I seguenti argomenti ti aiutano a conoscere le chiavi esterne e come utilizzarle:

• Panoramica delle chiavi esterne in Spanner
• Tipi di chiavi esterne
• Confronto dei tipi di chiavi esterne
• Scegliere il tipo di chiave esterna da utilizzare
• Utilizzare le chiavi esterne applicate
• Utilizzare chiavi esterne informative
• Indici di supporto
• Modifiche allo schema di lunga durata

Panoramica delle chiavi esterne in Spanner

Le chiavi esterne definiscono le relazioni tra le tabelle. Puoi utilizzare le chiavi esterne per assicurarti che l'integrità dei dati di queste relazioni in Spanner venga mantenuta.

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 le 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 la validità degli ordini nel tuo sistema.

• 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. Per impostazione predefinita, Spanner utilizza le chiavi esterne per applicare l'integrità referenziale.

Definire 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 chiavi esterne. FK_CustomerOrder si aspetta che tutte le righe di Orders abbiano un CustomerId valido. La chiave esterna FK_ProductOrder prevede 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	Si prevede che tutte le righe in Orders abbiano un valore valido per CustomerId	Un cliente valido effettua un ordine
FK_ProductOrder	Si prevede che tutte le righe in Orders abbiano un valore valido per ProductId	È stato effettuato un ordine per un prodotto valido

Spanner applica i vincoli specificati utilizzando chiavi esterne applicate. Ciò significa che Spanner non riesce a completare qualsiasi transazione che tenta di inserire o aggiornare una riga nella tabella Orders con 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.

A differenza delle chiavi esterne applicate, Spanner non convalida i vincoli sulle chiavi esterne informative. Ciò significa che se in questo scenario utilizzi una chiave esterna informativa, una transazione che tenta di inserire o aggiornare una riga nella tabella Orders con un valore CustomerId o ProductId non trovato nelle tabelle Customers e Products non viene convalidata e la transazione non fallisce. Inoltre, a differenza delle chiavi esterne obbligatorie, le chiavi esterne informative sono supportate solo da GoogleSQL e non da PostgreSQL.

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 sono limitati allo 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. Ad esempio, la prima colonna di riferimento fa riferimento alla prima colonna di riferimento e la seconda colonna di riferimento fa riferimento alla seconda colonna di riferimento.

• 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 fa riferimento deve esistere prima di creare una chiave esterna. Ciò significa che almeno una delle chiavi esterne 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 si fa riferimento per una chiave esterna coincidono con le 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.

• 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.

Tipi di chiavi esterne

Esistono due tipi di chiavi esterne: obbligatorie e informative. Le chiavi esterne obbligatorie sono predefinite e applicano l'integrità referenziale. Le chiavi esterne informative non applicano l'integrità referenziale e sono ideali per dichiarare il modello di dati logico previsto per l'ottimizzazione delle query. Per maggiori dettagli, consulta le seguenti sezioni sulle chiavi esterne obbligatorie e informative e la tabella di confronto dei tipi di chiavi esterne.

Chiavi esterne applicate

Le chiavi esterne obbligatorie, il tipo di chiave esterna predefinito in Spanner, obbligano l'integrità referenziale. Poiché le chiavi esterne applicate richiedono l'integrità referenziale, i tentativi di eseguire le seguenti operazioni non vanno a buon fine:

• L'aggiunta di una riga a una tabella di riferimento con un valore della chiave esterna che non esiste nella tabella a cui fa riferimento non va a buon fine.

• L'eliminazione di una riga da una tabella a cui viene fatto riferimento da righe nella tabella di riferimento non va a buon fine.

Tutte le chiavi esterne PostgreSQL vengono applicate. Le chiavi esterne di GoogleSQL vengono applicate per impostazione predefinita. Poiché le chiavi esterne vengono applicate per impostazione predefinita, l'utilizzo della parola chiave ENFORCED per specificare che una chiave esterna GoogleSQL viene applicata è facoltativo.

Chiavi esterne informative

Le chiavi esterne informative vengono utilizzate per dichiarare il modello di dati logico previsto per l'ottimizzazione delle query. Sebbene le chiavi delle tabelle a cui si fa riferimento debbano essere univoche per le chiavi esterne informative, l'integrità referenziale non viene applicata. Se vuoi convalidare in modo selettivo l'integrità referenziale quando utilizzi chiavi esterne informative, devi gestire la logica di convalida lato client. Per ulteriori informazioni, consulta la sezione Utilizzare le chiavi esterne informative.

Utilizza la parola chiave NOT ENFORCED per specificare che una chiave esterna GoogleSQL è informativa. PostgreSQL non supporta le chiavi esterne informative.

Confronto dei tipi di chiavi esterne

Sia le norme obbligatorie sia quelle informative hanno dei vantaggi. Le sezioni seguenti confrontano i due tipi di chiavi esterne e includono alcune best practice.

Differenze di chiavi esterne di alto livello

A livello generale, di seguito sono riportate alcune delle differenze tra le chiavi esterne obbligatorie e quelle informative:

• Applicazione. Le chiavi esterne applicate convalidano e garantiscono l'integrità referenziale sulle scritture. Le chiavi esterne informative non convalidano né garantiscono l'integrità referenziale.

• Spazio di archiviazione. Le chiavi esterne applicate potrebbero richiedere spazio di archiviazione aggiuntivo per l'indice di supporto nella tabella con vincoli.

• Velocità effettiva di scrittura. Le chiavi esterne applicate potrebbero comportare un maggiore overhead nel percorso di scrittura rispetto alle chiavi esterne informative.

• Ottimizzazione delle query. Entrambi i tipi di chiavi esterne possono essere utilizzati per l'ottimizzazione delle query. Quando l'ottimizzatore è autorizzato a utilizzare chiavi esterne informative, i risultati delle query potrebbero non riflettere i dati effettivi se i dati non corrispondono alle relazioni delle chiavi esterne informative (ad esempio, se alcune chiavi con vincoli non hanno chiavi di riferimento corrispondenti nella tabella di riferimento).

Tabella delle differenze delle chiavi esterne

La seguente tabella elenca le differenze dettagliate tra le chiavi esterne obbligatorie e informative:

	Chiavi esterne applicate	Chiavi esterne informative
Parole chiave	ENFORCED	NOT ENFORCED
Supportato da GoogleSQL	Sì. Le chiavi esterne in GoogleSQL vengono applicate per impostazione predefinita.	Sì.
Supportato da PostgreSQL	Sì. Le chiavi esterne in PostgreSQL possono essere solo applicate.	No.
Archiviazione	Le chiavi esterne applicate richiedono spazio di archiviazione per un massimo di due indici di supporto.	Le chiavi esterne informative richiedono spazio di archiviazione per un massimo di un indice di supporto.
Se necessario, crea indici di supporto sulle colonne delle tabelle a cui si fa riferimento	Sì.	Sì.
Se necessario, crea indici di supporto nelle colonne della tabella di riferimento	Sì.	No.
Supporto delle azioni delle chiavi esterne	Sì.	No.
Convalida e applica l'integrità referenziale	Sì.	No. L'assenza di convalida migliora le prestazioni di scrittura, ma può influire sui risultati delle query quando vengono utilizzate chiavi esterne informative per l'ottimizzazione delle query. Puoi utilizzare la convalida lato client o una chiave esterna impostata per garantire l'integrità referenziale.

Scegli il tipo di chiave esterna da utilizzare

Puoi utilizzare le seguenti linee guida per decidere quale tipo di chiave esterna utilizzare:

Ti consigliamo di iniziare con le chiavi esterne applicate. Le chiavi esterne applicate mantengono i dati e il modello logico sempre coerenti. Le chiavi esterne obbligatorie sono l'opzione consigliata, a meno che non funzionino per il tuo caso d'uso.

Ti consigliamo di prendere in considerazione le chiavi esterne informative se ciascuna delle seguenti condizioni è vera:

• Vuoi utilizzare il modello di dati logico descritto dalla chiave esterna informativa nell'ottimizzazione delle query.

• Il mantenimento di un'integrità referenziale rigorosa non è pratico o influisce in modo significativo sul rendimento. Di seguito sono riportati alcuni esempi di casi in cui potresti dover prendere in considerazione l'utilizzo di una chiave esterna informativa:

  • L'origine dati a monte segue un modello di coerenza finale. In questo caso, gli aggiornamenti apportati nel sistema di origine potrebbero non essere applicati immediatamente in Spanner. Poiché gli aggiornamenti potrebbero non essere immediati, potrebbero verificarsi brevi incoerenze nelle relazioni di chiavi esterne.

  • I dati contengono righe a cui si fa riferimento che hanno un numero elevato di relazioni di riferimento. Gli aggiornamenti di queste righe possono utilizzare molte risorse perché Spanner deve convalidare o, in alcuni casi, eliminare tutte le righe correlate al mantenimento dell'integrità referenziale. In questo scenario, gli aggiornamenti potrebbero influire sulle prestazioni di Spanner e rallentare le transazioni simultanee.

• La tua applicazione può gestire potenziali incongruenze nei dati e il loro impatto sui risultati delle query.

Utilizzare chiavi esterne informative

I seguenti argomenti sono solo per chiavi esterne informative. Per gli argomenti che si applicano sia alle chiavi esterne informative che a quelle applicate, consulta quanto segue:

• Modifiche allo schema di lunga durata
• Indici di supporto

Creare una nuova tabella con una chiave esterna informativa

Puoi creare e rimuovere le chiavi esterne informative dal database Spanner utilizzando le istruzioni DDL. Aggiungi le chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere o rimuovere le chiavi esterne da una tabella esistente con l'istruzione ALTER TABLE.

L'esempio seguente crea una nuova tabella con una chiave esterna informativa utilizzando GoogleSQL. Le chiavi esterne informative non sono supportate da PostgreSQL.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL

Not Supported

Per altri esempi su come creare e gestire le chiavi esterne, consulta Creare e gestire le relazioni con le chiavi esterne. Per ulteriori informazioni sulle istruzioni DDL, consulta la documentazione di riferimento DDL.

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.

Utilizzare chiavi esterne informative per l'ottimizzazione delle query

Sia le chiavi esterne applicate sia le chiavi esterne informative possono essere utilizzate dall'optimizzatore delle query per migliorare il rendimento delle query. L'utilizzo di chiavi esterne informative consente di sfruttare i piani di query ottimizzati senza il sovraccarico dell'applicazione rigorosa dell'integrità referenziale.

Se consenti all'ottimizzatore delle query di utilizzare informazioni sulle chiavi esterne informative, è importante capire che la correttezza dell'ottimizzazione dipende dalla presenza di dati coerenti con il modello logico descritto dalle chiavi esterne informative. Se esistono incoerenze, i risultati delle query potrebbero non riflettere i dati effettivi. Un esempio di incoerenza avviene quando un valore nella colonna vincolata non ha un valore corrispondente in una colonna a cui viene fatto riferimento.

Per impostazione predefinita, lo strumento di ottimizzazione delle query utilizza le chiavi esterne NOT ENFORCED. Per modificarlo, imposta l'opzione database use_unenforced_foreign_key_for_query_optimization su false. Di seguito è riportato un esempio di GoogleSQL che lo dimostra (le chiavi esterne informative non sono disponibili in PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

L'opzione di suggerimento per l'istruzione di query booleana @{use_unenforced_foreign_key} sostituisce l'opzione di database su base query che controlla se l'ottimizzatore utilizza le chiavi esterne NOT ENFORCED. La disattivazione di questo suggerimento o dell'opzione di database può essere utile per la risoluzione dei problemi relativi ai risultati delle query imprevisti. Di seguito viene mostrato come utilizzare @{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Utilizza le chiavi esterne applicate

I seguenti argomenti riguardano solo le chiavi esterne applicate. Per gli argomenti che si applicano sia alle chiavi esterne informative che a quelle applicate, consulta quanto segue:

• Modifiche allo schema che richiedono molto tempo
• Indici di supporto

Creare una nuova tabella con una chiave esterna applicata

Puoi creare, rimuovere e applicare le chiavi esterne nel tuo database Spanner utilizzando DDL. Aggiungi le chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere o rimuovere una chiave esterna da una tabella esistente con l'istruzione ALTER TABLE.

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 applicata.

GoogleSQL

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

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 le relazioni con le chiavi 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 possono essere definite solo sulle chiavi esterne applicate.

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 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 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 di caratteristiche delle azioni delle chiavi esterne 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 chiavi esterne con 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.

Convalida dei vincoli

La convalida dei vincoli si applica solo alle chiavi esterne applicate.

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

Un valore inserito nella colonna di riferimento viene confrontato con i 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 tutti i vincoli di referenza delle chiavi esterne applicati applicabili quando si tenta di aggiornare i dati utilizzando istruzioni DML o un'API. Tutte le modifiche in attesa vengono annullate se i vincoli sono invalidi.

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 delle chiavi esterne applicate viene posticipata fino al commit della transazione. In questo caso, è consentito inserire prima le righe di riferimento.

Per ogni transazione viene valutata la presenza di modifiche che influiscono sui vincoli di chiave estranea applicati. Queste valutazioni potrebbero richiedere richieste aggiuntive al server. Gli indici di supporto richiedono inoltre tempi di elaborazione aggiuntivi per valutare le modifiche delle transazioni e per gestire gli indici. È inoltre necessario spazio di archiviazione aggiuntivo per ogni indice.

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 un vincolo di chiave esterna con un'azione di eliminazione a cascata a una tabella o la creazione di una tabella con vincoli di 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 chiavi esterne

L'eliminazione di un numero elevato di record utilizzando un'eliminazione a cascata di chiavi esterne 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 a cascata delle chiavi esterne, elimina esplicitamente le righe dalle tabelle secondarie prima di eliminare la riga dalle tabelle principali. In questo modo, la transazione non andrà a buon fine a causa del limite di mutazioni.

Confronto delle chiavi esterne applicate e dell'interlacciamento 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 database con chiavi esterne applicate

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 applicata 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 Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

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 Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

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. L'interlacciamento fisico di 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 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 interfoliazione 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 applicate 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 applicate
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 passa se non è stata 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ì

Indici di supporto

Le chiavi esterne non utilizzano gli indici creati dall'utente. ma creano i propri indici di supporto. Le chiavi esterne obbligatorie e informative creano indici di supporto in modo diverso in Spanner:

• Per le chiavi esterne applicate, 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.

• Per le chiavi esterne informative, Spanner può creare fino a un indice di supporto quando necessario per le colonne di riferimento. Le chiavi esterne informative non creano un indice di supporto per le colonne di riferimento.

Sia per le chiavi esterne applicate che per quelle informative, una chiave esterna solitamente fa riferimento alle chiavi primarie della tabella a cui fa riferimento, pertanto in genere non è necessario un indice per la tabella a cui fa riferimento. Per questo motivo, le chiavi esterne informative in genere non hanno indici di supporto. Se necessario, l'indice di supporto creato per la tabella a cui si fa 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.

Le chiavi esterne informative non hanno un indice di supporto per la tabella di riferimento. Per le chiavi esterne applicate, 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 ciascuna di esse. Gli indici di supporto vengono eliminati quando vengono eliminate le chiavi esterne che li utilizzano. Non puoi alterare o eliminare gli indici di supporto.

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

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

Modifiche allo schema a lungo termine

L'aggiunta di una chiave esterna applicata 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 lungo termine non è completata.

La tabella seguente mostra cosa succede in Spanner quando una chiave esterna obbligatoria e una informativa si trovano in una tabella nuova o esistente:

Tipo di tabella	Chiave esterna applicata	Chiave esterna informativa
Nuovo	Spanner esegue il backfill degli indici a cui si fa riferimento in base alle esigenze per ogni chiave esterna.	Spanner esegue il backfill degli indici a cui si fa riferimento in base alle esigenze per ogni chiave esterna.
Esistente	Spanner esegue il backfill degli indici di riferimento e a cui si fa riferimento in base alle esigenze. Spanner convalida anche i dati esistenti nella tabella per verificare che siano conformi al vincolo di integrità referenziale della chiave esterna. La modifica dello schema non va a buon fine se uno dei dati non è valido.	Spanner esegue il backfill dell'indice a cui si fa riferimento in base alle esigenze e non convalida i dati esistenti nella tabella.

Non è supportato quanto segue:

• Aggiunta di un'azione di chiave esterna a una limitazione di chiave esterna applicata esistente.
• Modifica dell'applicazione di una chiave esterna esistente.

In entrambi i casi, ti consigliamo di procedere come segue:

1. Aggiungi una nuova limitazione con l'azione o l'applicazione forzata richiesta.
2. Rimuovi la vecchia limitazione.

L'aggiunta di un nuovo vincolo e l'eliminazione del vecchio vincolo impedisce un problema di Operazione di alterazione vincolo di lunga durata. Ad esempio, supponiamo che tu voglia aggiungere un'azione DELETE CASCADE a una chiave esterna esistente. Dopo aver creato la nuova chiave estranea con l'azione ON DELETE CASCADE, l'effetto di entrambi i vincoli è un'azione DELETE CASCADE. Dopodiché puoi eliminare in sicurezza la vecchia limitazione.

L'eliminazione di una limitazione può comportare l'eliminazione degli indici di supporto delle chiavi esterne se gli indici non sono utilizzati da altre limitazioni delle chiavi esterne. Per questo motivo, se elimini prima il vecchio vincolo, l'aggiunta dello stesso vincolo di chiave esterna con un'azione in un secondo momento potrebbe comportare operazioni che richiedono molto tempo, come il backfill degli indici, la convalida dei vincoli di indice univoco o la convalida dei vincoli di referenza delle chiavi esterne.

Nota: se non elimini la precedente limitazione della chiave esterna, le limitazioni della chiave esterna aggiuntive richiedono la convalida, il che causa un overhead non necessario. Inoltre, se una violazione del vincolo UNIQUE impedisce la creazione dell'indice a cui si fa riferimento, l'aggiunta del nuovo vincolo potrebbe non riuscire.

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

Passaggi successivi

• Scopri di più sulla creazione e gestione delle relazioni con le chiavi esterne.

• Scopri di più sullo schema di informazioni.