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 scoprire le chiavi esterne e come utilizzarle:

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.

Supponi di essere uno sviluppatore di lead per 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 mostra la struttura di base del database per l'applicazione.

Struttura di base del database di elaborazione degli ordini.

Figura 1. Diagramma di un database di elaborazione degli ordini

Definisci una tabella Customers per archiviare i dati del cliente, una tabella Orders per monitorare tutti gli ordini e una tabella Products per archiviare le informazioni su ogni prodotto.

La Figura 1 mostra anche i collegamenti tra le tabelle che corrispondono alle seguenti relazioni nel mondo reale:

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

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

Quando applichi queste regole, o vincoli, mantieni l'integrità referenziale dei tuoi dati. Quando un database mantiene l'integrità referenziale, tutti i tentativi di aggiungere dati non validi, che comporterebbero collegamenti o riferimenti non validi tra i dati, non vanno a buon fine. L'integrità referenziale previene gli errori degli utenti. Per impostazione predefinita, Spanner utilizza le chiavi esterne per applicare l'integrità referenziale.

Definisci l'integrità referenziale con le chiavi esterne

Di seguito viene esaminato di nuovo l'esempio di elaborazione degli ordini, con maggiori dettagli aggiunti alla progettazione, come mostrato nella Figura 2.

Schema del database con chiavi esterne

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

Ora la struttura mostra i nomi e i tipi delle colonne in ogni tabella. La tabella Orders definisce anche due relazioni di chiave esterna. FK_CustomerOrder prevede 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 in base alle regole del mondo reale che vuoi applicare.

Nome chiave esterna Vincolo Descrizione nel mondo reale
FK_CustomerOrder Prevede che tutte le righe di Orders abbiano un valore CustomerId valido Un cliente valido effettua un ordine
FK_ProductOrder Prevede che tutte le righe di Orders abbiano un valore ProductId valido È stato effettuato un ordine per un prodotto valido

Spanner applica i vincoli specificati utilizzando le chiavi esterne applicate. Ciò significa che Spanner non riesce a eseguire qualsiasi transazione che tenti di inserire o aggiornare una riga nella tabella Orders che contiene un CustomerId o un ProductId non trovato nelle tabelle Customers e Products. Inoltre, le transazioni che tentano di aggiornare o eliminare righe nelle tabelle Customers e Products che invaliderebbero gli ID nella tabella Orders non vanno a buon fine. 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 utilizzi una chiave esterna informativa in questo scenario, una transazione che tenta di inserire o aggiornare una riga nella tabella Orders che contiene un CustomerId o un ProductId non trovato nelle tabelle Customers e Products non viene convalidata e la transazione non ha esito negativo. Inoltre, a differenza delle chiavi esterne applicate, le chiavi esterne informative sono supportate solo da GoogleSQL e non da PostgreSQL.

Caratteristiche della 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 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 interrogare il nome generato da INFORMATION_SCHEMA di Spanner. I nomi dei vincoli sono inclusi nello schema, insieme ai nomi di tabelle e indici, e devono essere univoci all'interno dello schema.

  • Il numero di colonne di riferimento e a cui viene fatto riferimento deve essere lo stesso. L'ordine è importante. Ad esempio, la prima colonna di riferimento si riferisce alla prima colonna a cui viene fatto riferimento, mentre la seconda colonna di riferimento si riferisce alla seconda colonna a cui viene fatto riferimento.

  • Una colonna di riferimento e la relativa colonna 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 a 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 le tabelle in cui due tabelle fanno riferimento l'una all'altra, direttamente o indirettamente. La tabella a cui viene fatto 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 viene fatto riferimento devono essere univoche. Spanner utilizza PRIMARY KEY della tabella a cui viene fatto riferimento se le colonne a cui viene fatto riferimento per una chiave esterna corrispondono alle colonne della chiave primaria della tabella a cui viene fatto riferimento. Se Spanner non può utilizzare la chiave primaria della tabella a cui viene fatto riferimento, crea un UNIQUE NULL_FILTERED INDEX sulle colonne a cui viene fatto riferimento.

  • Le chiavi esterne non utilizzano gli indici secondari che hai creato. creano invece i propri indici di backing. Gli indici di supporto sono utilizzabili nelle valutazioni delle query, incluse le direttive force_index esplicite. Puoi eseguire query sui nomi degli indici di supporto da INFORMATION_SCHEMA di Spanner. Per saperne di più, consulta Indici di backup.

Tipi di chiavi esterne

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

Chiavi esterne applicate

Le chiavi esterne applicate, il tipo di chiave esterna predefinito in Spanner, applicano l'integrità referenziale. Poiché le chiavi esterne forzate applicano 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 di chiave esterna che non esiste nella tabella a cui viene fatto 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 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 dei dati logico previsto per l'ottimizzazione delle query. Sebbene le chiavi delle tabelle a cui viene fatto riferimento debbano essere univoche per le chiavi esterne informative, l'integrità referenziale non viene applicata. Se vuoi convalidare selettivamente l'integrità referenziale quando utilizzi chiavi esterne informative, devi gestire la logica di convalida lato client. Per saperne di più, consulta 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 chiave esterna

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

Differenze di alto livello tra le chiave esterna

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

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

  • Archiviazione. Le chiavi esterne applicate potrebbero richiedere spazio di archiviazione aggiuntivo per l'indice di supporto nella tabella vincolata.

  • Throughput di scrittura. Le chiavi esterne applicate potrebbero comportare un overhead maggiore 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 della query potrebbero non riflettere i dati effettivi se i dati non corrispondono alle relazioni tra chiave esterna informative (ad esempio, se alcune chiavi vincolate non hanno chiavi di riferimento corrispondenti nella tabella di riferimento).

Tabella delle differenze tra le chiavi esterne

La seguente tabella elenca le differenze dettagliate tra le chiavi esterne applicate 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 applicate solo. 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.
Crea indici di supporto sulle colonne della tabella a cui viene fatto riferimento quando necessario Sì. Sì.
Crea indici di supporto sulle colonne della tabella di riferimento quando necessario Sì. No.
Supporto per le azioni della chiave esterna Sì. No.
Convalida e applica l'integrità referenziale Sì. No. La mancata convalida migliora le prestazioni di scrittura, ma può influire sui risultati delle query quando le chiavi esterne informative vengono utilizzate per l'ottimizzazione delle query. Puoi utilizzare la convalida lato client o una chiave esterna forzata 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 forzate mantengono i dati e il modello logico coerenti in ogni momento. Le chiavi esterne applicate sono l'opzione consigliata, a meno che non siano adatte al tuo caso d'uso.

Ti consigliamo di prendere in considerazione le chiavi esterne informative se si verificano tutte le seguenti condizioni:

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

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

    • L'origine dati upstream 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 chiave esterna.

    • I tuoi dati contengono righe a cui viene fatto riferimento che hanno un numero elevato di relazioni di riferimento. Gli aggiornamenti a 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 dei dati e il loro impatto sui risultati delle query.

Utilizzare le chiavi esterne informative

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

Crea una nuova tabella con una chiave esterna informativa

Puoi creare e rimuovere le chiavi esterne informative dal tuo database Spanner utilizzando le istruzioni DDL. Aggiungi chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Allo stesso modo, puoi aggiungere o rimuovere 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 di come creare e gestire le chiavi esterne, vedi Creazione e gestione di chiave esterna esterne. Per saperne di più sulle istruzioni DDL, consulta il riferimento DDL.

Utilizzare le chiavi esterne informative per l'ottimizzazione delle query

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

Se abiliti l'ottimizzatore delle query a utilizzare le 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 incongruenze, i risultati della query potrebbero non riflettere i dati effettivi. Un esempio di incoerenza si verifica quando un valore nella colonna vincolata non ha un valore corrispondente in una colonna a cui viene fatto riferimento.

Per impostazione predefinita, l'ottimizzatore delle query utilizza le chiavi esterne NOT ENFORCED. Per modificare questo comportamento, imposta l'opzione del 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
);

Il suggerimento per l'istruzione di query booleana @{use_unenforced_foreign_key} esegue l'override dell'opzione di database in base alla 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;

Utilizzare 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, vedi quanto segue:

Crea una nuova tabella con una chiave esterna applicata

Puoi creare e rimuovere chiavi esterne applicate dal tuo database Spanner utilizzando DDL. Aggiungi chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Allo stesso modo, puoi aggiungere o rimuovere una chiave esterna da una tabella esistente con l'istruzione ALTER TABLE.

Crea e rimuovi le chiavi esterne dal database Spanner utilizzando DDL. Aggiungi chiavi esterne a una nuova tabella con l'istruzione CREATE TABLE. Allo stesso modo, puoi aggiungere o rimuovere una chiave esterna da una tabella esistente 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 di come creare e gestire le chiavi esterne, vedi Creazione e gestione di chiave esterna esterne.

Azioni chiave esterna

Le azioni della chiave esterna possono essere definite solo per le chiavi esterne applicate.

Le azioni della chiave esterna controllano cosa succede alla colonna vincolata quando la colonna a cui fa riferimento viene eliminata o aggiornata. Spanner supporta l'utilizzo dell'azione ON DELETE CASCADE. Con l'azione ON DELETE CASCADE della chiave esterna, quando elimini una riga che contiene una chiave esterna a cui viene fatto riferimento, anche tutte le righe che fanno riferimento a quella chiave vengono eliminate 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. Allo stesso modo, 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 delle caratteristiche delle azioni di chiave esterna in Spanner.

  • Le azioni della chiave esterna sono ON DELETE CASCADE o ON DELETE NO ACTION.

  • Puoi eseguire query su INFORMATION_SCHEMA per trovare vincoli di chiave esterna che hanno un'azione.

  • L'aggiunta di un'azione di chiave esterna a un vincolo di chiave esterna esistente non è supportata. Devi aggiungere un nuovo vincolo di chiave esterna con un'azione.

Convalida del vincolo

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

Spanner convalida i vincoli di chiave esterna applicati quando viene eseguito il 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 vengono selezionate, il che significa che puoi aggiungerle alla tabella di riferimento.

Spanner convalida tutti i vincoli referenziali di chiave esterna applicabili quando tenta di aggiornare i dati utilizzando istruzioni DML o un'API. Tutte le modifiche in attesa 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 viene fatto riferimento prima di inserire le righe che fanno riferimento. Quando utilizzi un'API di mutazione, le mutazioni vengono memorizzate nel buffer fino al commit della transazione. La convalida delle chiave esterna forzata viene posticipata fino al commit della transazione. In questo caso, è consentito inserire prima le righe di riferimento.

Ogni transazione viene valutata per le modifiche che influiscono sui vincoli di chiave esterna applicati. Queste valutazioni potrebbero richiedere ulteriori richieste al server. Gli indici di backup richiedono anche tempi di elaborazione aggiuntivi per valutare le modifiche alle transazioni e per mantenere gli indici. È necessario anche spazio di archiviazione aggiuntivo per ogni indice.

Azione di eliminazione a cascata a lunga esecuzione

Quando elimini una riga da una tabella a cui viene fatto riferimento, Spanner deve eliminare tutte le righe delle tabelle di riferimento che fanno riferimento alla riga eliminata. Ciò può portare a un effetto a cascata, in cui una singola operazione di eliminazione comporta migliaia di altre operazioni di eliminazione. L'aggiunta di un vincolo di chiave esterna con azione di eliminazione a cascata a una tabella o la creazione di una tabella con vincoli di chiave esterna con azione di eliminazione 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 utilizzando un'eliminazione a cascata della chiave esterna può influire sulle prestazioni. Questo 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 di chiave esterna, elimina esplicitamente le righe dalle tabelle secondarie prima di eliminare la riga dalle tabelle principali. Ciò impedisce che la transazione non vada a buon fine a causa del limite di mutazione.

Confronto tra chiavi esterne applicate e interleaving 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 padre in alcune relazioni sia come figlio in altre. Tuttavia, una relazione di chiave esterna non implica la co-location delle tabelle nel livello di archiviazione.

Considera un esempio che utilizza una tabella Orders definita come segue:

Schema del database con chiavi esterne

Figura 3. Diagramma dello schema del database con chiavi esterne applicate

Il design della Figura 3 presenta alcune limitazioni. Ad esempio, ogni ordine può contenere un solo articolo dell'ordine.

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

Ecco come definire la tabella OrderItems, intervallata da 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 mostra una rappresentazione visiva dello schema del database aggiornato a seguito dell'introduzione di questa nuova tabella, OrderItems, intercalata con Orders. Qui puoi anche vedere la relazione uno-a-molti tra le due tabelle.

Schema del database che mostra una relazione one-to-many tra gli ordini e la nuova tabella OrderItems intercalata

Immagine 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 alternate e quindi collocate insieme agli ordini. L'interleaving fisico di Orders e OrderItems in questo modo può migliorare le prestazioni, unendo in modo efficace le tabelle e consentendoti di accedere alle righe correlate insieme, 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. Eliminazioni a cascata così grandi funzionano bene per le tabelle con una relazione "interfoliata nel parent", ma non per le tabelle con una relazione di chiave esterna. Se hai una relazione di chiave esterna e devi eliminare un numero elevato di righe, devi eliminare esplicitamente le righe dalle tabelle secondarie.

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 viene fatto riferimento attiva l'eliminazione di milioni di righe, devi progettare lo schema con un'azione di eliminazione a cascata con "interleaved in parent".

Tabella di confronto

La seguente tabella riassume il confronto tra le chiavi esterne applicate e l'interleaving delle tabelle. Puoi utilizzare queste informazioni per decidere cosa è più adatto al tuo design.

Tipo di relazione principale-secondario Interfoliazione delle tabelle Chiavi esterne applicate
Può utilizzare i tasti principali Sì Sì
Può utilizzare colonne non di chiave primaria No Sì
Numero di genitori supportati 0 .. 1 0 .. N
Archivia insieme i dati principali e secondari Sì No
Supporta l'eliminazione a cascata Sì Sì
Modalità di corrispondenza nulla Supera il test 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.		 Supera il test se uno qualsiasi dei valori di riferimento è null.
Supera il test se tutti i valori di riferimento sono non null e la tabella a cui viene fatto riferimento ha una riga con valori uguali a quelli di riferimento.
Non supera il test se non è stata trovata alcuna riga corrispondente.
Tempistica di applicazione Per operazione quando si utilizza l'API Mutation.
Per istruzione quando utilizzi DML.		 Per transazione quando utilizzi l'API Mutation.
Per istruzione quando utilizzi DML.
Può essere rimosso No. L'interleaving delle tabelle non può essere rimosso dopo la creazione, a meno che non elimini l'intera tabella secondaria. Sì

Indici di supporto

Le chiavi esterne non utilizzano indici creati dall'utente. ma creano i propri indici di supporto. Le chiavi esterne applicate 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 secondo per le colonne a cui viene fatto riferimento.

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

Per le chiavi esterne applicate e informative, una chiave esterna in genere fa riferimento alle chiavi primarie della tabella a cui fa riferimento, quindi un indice per la tabella a cui fa riferimento in genere non è necessario. 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 viene fatto riferimento è un indice UNIQUE NULL_FILTERED. La creazione della chiave esterna non riesce 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. Gli indici di supporto vengono eliminati quando vengono eliminate le chiavi esterne che li utilizzano. Non puoi modificare o eliminare gli indici di supporto.

Spanner utilizza lo schema informativo di ogni database per memorizzare i metadati sugli 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.

Al di fuori delle query SQL che richiamano direttamente lo schema informativo, la consoleGoogle Cloud non mostra alcuna informazione sugli indici di supporto di un database.

Modifiche allo schema a lunga esecuzione

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 di lunga durata. Nel caso di una nuova tabella, questa non è scrivibile finché l'operazione a lunga esecuzione non viene completata.

La seguente tabella mostra cosa succede in Spanner quando una chiave esterna applicata 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 viene fatto riferimento in base alle esigenze per ogni chiave esterna. Spanner esegue il backfill degli indici a cui viene fatto riferimento in base alle esigenze per ogni chiave esterna.
Esistente Spanner esegue il backfill degli indici di riferimento e a cui viene fatto riferimento in base alle esigenze. Spanner convalida anche i dati esistenti nella tabella per garantire che siano conformi al vincolo di integrità referenziale della chiave esterna. La modifica dello schema non va a buon fine se i dati non sono validi. Spanner esegue il backfill dell'indice a cui viene fatto riferimento in base alle necessità e non convalida i dati esistenti nella tabella.

Le seguenti funzionalità non sono supportate:

  • Aggiunta di un'azione di chiave esterna a un vincolo di chiave esterna applicato esistente.
  • Modifica dell'applicazione di una chiave esterna esistente.

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

  1. Aggiungi un nuovo vincolo con l'azione o l'applicazione richieste.
  2. Elimina il vecchio vincolo.

L'aggiunta di un nuovo vincolo e l'eliminazione del vecchio vincolo impediscono un problema di operazione Alter Constraint a lunga esecuzione. Ad esempio, supponiamo di voler aggiungere un'azione DELETE CASCADE a una chiave esterna esistente. Dopo aver creato la nuova chiave esterna con l'azione ON DELETE CASCADE, l'effetto di entrambi i vincoli è un'azione DELETE CASCADE. Dopodiché, puoi eliminare il vecchio vincolo in sicurezza.

L'eliminazione di un vincolo può comportare l'eliminazione degli indici di supporto delle chiavi esterne se gli indici non vengono utilizzati da altri vincoli di chiave esterna. Per questo motivo, se elimini prima il vecchio vincolo, l'aggiunta successiva dello stesso vincolo di chiave esterna con un'azione potrebbe comportare operazioni di lunga durata, come il riempimento degli indici, la convalida dei vincoli di indice univoco o la convalida dei vincoli referenziali di chiave esterna.

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

Passaggi successivi