Chiavi esterne

Questo argomento descrive le chiavi esterne in Spanner e come utilizzarle per applicare l'integrità referenziale nella tua soluzione di database.

Panoramica

Le chiavi esterne consentono di definire le relazioni tra le tabelle. Spanner garantisce il mantenimento dell'integrità dei dati di queste relazioni.

Immagina di essere lo sviluppatore principale per un'attività di e-commerce. Stai progettando un database per l'elaborazione degli ordini dei clienti. Il database deve archiviare le informazioni su ogni ordine, cliente e prodotto. La Figura 1 illustra 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 tenere traccia di tutti gli ordini effettuati e una tabella Products per archiviare le informazioni su ciascun prodotto che i clienti possono ordinare.

La Figura 1 mostra anche i link tra le tabelle mappate alle seguenti relazioni reali:

• Un cliente ha effettuato un ordine

• È stato effettuato un ordine per un prodotto

Decidi che il tuo database deve applicare le regole seguenti per assicurarti che gli ordini nel nostro sistema siano validi.

• Impossibile creare un ordine per un cliente che non esiste.

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

Quando applichiamo queste regole, o vincoli, indichiamo di mantenere l'integrità referenziale dei nostri dati. Quando un database mantiene l'integrità referenziale, tutti i tentativi di aggiungere dati non validi, determinando collegamenti o riferimenti non validi tra i dati, non andranno a buon fine. L'integrità referenziale previene gli errori dell'utente. Spanner applica l'integrità referenziale tramite le chiavi esterne.

Forza l'integrità referenziale con le chiavi esterne

Esaminiamo nuovamente il nostro esempio di elaborazione degli ordini, con ulteriori dettagli aggiunti al design, come mostrato nella Figura 2.

Figura 2. Diagramma dello schema del nostro database con chiavi esterne

La struttura ora mostra nomi e tipi di colonne in ogni tabella. La tabella Orders definisce anche due relazioni di chiave esterna. FK_CustomerOrder assicura che tutte le righe in Orders abbiano un valore CustomerID valido. La chiave esterna FK_ProductOrder assicura che tutti i valori ProductID nella tabella Orders siano validi. La seguente tabella riporta questi vincoli con le regole reali che vogliamo applicare.

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

Spanner non va a buon fine qualsiasi transazione che tenti di inserire o aggiornare una riga nella tabella Orders che abbia un valore CustomerID o ProductID non trovato nelle tabelle Customers e Products. Inoltre, non supera le transazioni che tentano di aggiornare o eliminare le righe nelle tabelle Customers e Products, il che renderebbe non validi gli ID nella tabella Orders. Per ulteriori dettagli su come Spanner convalida i vincoli, consulta la sezione Convalida del vincolo delle transazioni di seguito.

Definisci chiavi esterne

Le chiavi esterne vengono create e rimosse dal database Spanner utilizzando il DDL. Le chiavi esterne vengono aggiunte 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.

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.

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

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

• La tabella che definisce la chiave esterna è la tabella di riferimento, mentre le colonne delle chiavi esterne sono le colonne di riferimento.

• La chiave esterna fa riferimento alle colonne a cui si fa riferimento della tabella a cui si fa riferimento.

• Come nell'esempio precedente, puoi assegnare un nome a ogni vincolo di chiave esterna. Se non specifichi un nome, Spanner genera automaticamente un nome. È possibile eseguire query sul nome generato nel file INFORMATION_SCHEMA di Spanner. I nomi dei vincoli hanno come ambito lo schema, ad esempio quelli di tabelle e indici, 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 fa riferimento alla prima colonna di riferimento, la seconda alla seconda e così via.

• Una colonna di riferimento e la relativa controparte di riferimento devono essere dello stesso tipo. Inoltre, le colonne devono essere indicizzabili.

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

• Le colonne 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 di riferimento deve esistere prima di creare una chiave esterna, pertanto è necessario aggiungere almeno una delle chiavi esterne utilizzando l'istruzione ALTER TABLE.

• Le chiavi a cui viene fatto riferimento devono essere univoche. Spanner utilizza il valore PRIMARY KEY della tabella di riferimento se le colonne di riferimento della chiave esterna corrispondono alle colonne di 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 valore UNIQUE NULL_FILTERED INDEX sulle colonne di riferimento.

• Spanner potrebbe anche essere in grado di utilizzare la chiave primaria della tabella di riferimento, anche se meno comune. In caso contrario, Spanner crea un valore NULL_FILTERED INDEX sopra le colonne di riferimento.

• Le chiavi esterne non utilizzano indici secondari che hai creato, ma creano i propri indici di supporto. Questi indici di supporto sono utilizzabili nelle valutazioni delle query, incluse istruzioni esplicite force_index. È possibile eseguire query sul nome degli indici di supporto nel modulo INFORMATION_SCHEMA di Spanner. Per ulteriori informazioni, consulta la sezione Indici di supporto.

Azioni chiave esterna

Le azioni delle chiavi esterne consentono di controllare 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 di 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. Allo stesso modo, puoi utilizzare l'istruzione ALTER TABLE per aggiungere un'azione di chiave esterna a una tabella esistente o per rimuovere un'azione di chiave esterna. 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 di chiave esterna in Spanner.

• Le azioni dei tasti esterni sono IN ELIMINAZIONE A CASCATA o IN ELIMINAZIONE DI NESSUNA AZIONE.

• Puoi eseguire una query su INFORMATION_SCHEMA per trovare i 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.

Modifiche allo schema a lunga esecuzione

L'aggiunta di una chiave esterna a una tabella esistente o la creazione di una nuova tabella con una chiave esterna può portare a operazioni a lunga esecuzione. Nel caso di una nuova tabella, quest'ultima non può essere scrivibile fino al completamento dell'operazione a lunga esecuzione.

Per una nuova tabella con una chiave esterna, Spanner deve eseguire il backfill degli indici di 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, se necessario. Inoltre, Spanner convalida 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.

L'aggiunta di un'azione di chiave esterna a un vincolo esistente non è supportata. Ti consigliamo di procedere nel seguente modo:

1. Aggiungi un nuovo vincolo con azione.
2. Elimina il vincolo precedente senza azione.

In questo modo eviterai un problema relativo a 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 un vincolo potrebbe comportare l'eliminazione degli indici di supporto della chiave esterna se gli indici non sono utilizzati in altri vincoli di chiave esterna. Successivamente, se l'utente aggiunge lo stesso vincolo di chiave esterna con l'azione, potrebbe richiedere operazioni a lunga esecuzione, tra cui il backfill degli indici, la convalida di vincoli di indice univoci e la convalida dei vincoli di riferimento delle chiavi esterne.

Nota: se non elimini il vincolo di chiave esterna precedente, si verifica un sovraccarico dovuto alla convalida dei vincoli di chiave esterna aggiuntivi.

Nessuna delle modifiche allo schema precedenti può avere esito negativo se non è possibile creare l'indice di riferimento a causa di una violazione del vincolo UNIQUE.

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

Convalida del vincolo per una transazione

Spanner convalida i vincoli di chiave esterna quando è in corso il commit di una transazione o quando gli effetti delle scritture vengono resi visibili alle operazioni successive della transazione.

Un valore inserito nelle colonne di riferimento viene confrontato con i valori della tabella e delle colonne di riferimento. Le righe con NULL che fanno riferimento a valori non vengono selezionate, ossia puoi aggiungerle alla tabella di riferimento.

Spanner convalida tutti i vincoli di riferimento delle chiave esterna applicabili quando tenta di aggiornare i dati tramite istruzioni DML o API. Tutte le modifiche in attesa vengono sottoposte a rollback se eventuali vincoli non sono validi.

La convalida avviene immediatamente dopo ogni istruzione DML. Devi, ad esempio, inserire la riga di riferimento prima di inserire le relative righe di riferimento. Quando utilizzi un'API di mutazione, le mutazioni vengono memorizzate nel buffer finché non viene eseguito il commit della transazione. La convalida della chiave esterna viene differita fino al commit della transazione. In questo caso è consentito inserire prima le righe di riferimento.

Ogni transazione viene valutata per verificare la presenza di modifiche che influiscono sui vincoli delle chiave esterna. Queste valutazioni potrebbero richiedere richieste aggiuntive al server. Gli indici di supporto richiedono anche tempi di elaborazione aggiuntivi per valutare le modifiche alle transazioni e per mantenerli. È richiesto anche spazio di archiviazione aggiuntivo per ogni indice.

Indici di supporto

Le chiavi esterne non utilizzano 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 secondo per le colonne di riferimento. Tuttavia, una chiave esterna di solito fa riferimento alle chiavi primarie della tabella di riferimento, perciò il secondo indice nella tabella di riferimento in genere non è necessario.

L'indice di supporto per la tabella di riferimento è un indice UNIQUE NULL_FILTERED; la creazione della chiave esterna non riesce se dei dati esistenti violano il 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 singolo indice per tutte. Gli indici di supporto vengono eliminati quando vengono eliminate le chiavi esterne che li utilizzano. Gli utenti non possono modificare o eliminare gli indici di supporto.

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

Al di fuori delle query SQL che richiamano direttamente lo schema di informazioni, la console Google Cloud non mostra in caso contrario le informazioni sugli indici di supporto di un database.

Azione di eliminazione a cascata a lunga esecuzione

Quando elimini una riga da una tabella di 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 può comportare 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 l'azione di eliminazione a cascata può rallentare le operazioni di eliminazione.

Limite di mutazione superato per l'eliminazione a cascata di chiave esterna

L'eliminazione di un numero elevato di record utilizzando una cascata di eliminazione di chiave esterna può influire sulle prestazioni. Questo perché ogni record eliminato attiva l'eliminazione di tutti i record correlati che utilizzano chiavi esterne. Se il numero di mutazioni in una transazione supera 80.000, la transazione non va a buon fine.

Se devi eliminare un numero elevato di record utilizzando l'eliminazione a cascata di chiave esterna, devi eliminare in modo esplicito le righe dalle tabelle figlio prima di eliminare la riga dalle tabelle padre. Questo impedisce che la transazione non vada a buon fine 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ò migliorare notevolmente le prestazioni.

Le chiavi esterne sono una soluzione padre-figlio più generale 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 che come figlio in altre. Tuttavia, una relazione di chiave esterna non implica la collocazione delle tabelle nel livello di archiviazione.

Esaminiamo un esempio utilizzando lo schema di elaborazione degli ordini di cui abbiamo parlato in precedenza in questo argomento. La tabella Orders è stata definita come segue:

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

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

Immaginiamo che i nostri clienti ci diano la possibilità di ordinare più prodotti per ogni ordine. Possiamo migliorare il nostro design introducendo una tabella OrderItems che contiene una voce per ogni prodotto ordinato dal cliente. Possiamo introdurre un'altra chiave esterna per rappresentare questa nuova relazione one-to-many tra Orders e OrderItems. Tuttavia, sappiamo anche che spesso vorremo eseguire query su ordini e relativi articoli. La co-location di questi dati migliora le prestazioni, quindi creeremo la relazione padre-figlio utilizzando la funzionalità di interleaving delle tabelle di Spanner.

Ecco come definiamo la tabella OrderItems, con interleaving 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 in seguito all'introduzione di questa nuova tabella, OrderItems, con interleaving con Orders. Qui 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 interleaving e pertanto si trovano con gli ordini. L'interfoliazione fisica di Orders e OrderItems in questo modo può migliorare le prestazioni, pre-unendo efficacemente le tabelle e consentendoti di accedere alle righe correlate insieme, riducendo al minimo gli accessi ai dischi. Ad esempio, Spanner può eseguire join tramite 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 a cascata di grandi dimensioni funzionano bene per le tabelle con una relazione "con interleaving nella padre", 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 prima eliminare esplicitamente le righe dalle tabelle figlio.

Se hai una tabella utente con una relazione di chiave esterna a un'altra tabella e l'eliminazione di una riga dalla tabella di riferimento attiva l'eliminazione di milioni di righe, devi progettare lo schema con un'azione di eliminazione a cascata con "interleaving nella padre".

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 è ridondante e potrebbe consumare risorse di archiviazione e calcolo aggiuntive per verificare i vincoli e mantenere gli indici di supporto.

Tabella di confronto

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

Tipo di relazione genitore-figlio	Interleaving dei tavoli	Chiavi esterne
Può usare chiavi primarie	Sì	Sì
Può utilizzare colonne di chiave non 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 null	Passa se tutti i valori di riferimento non sono distinti da quelli di riferimento. I valori null non sono distinti dai valori null; i valori null sono distinti dai valori non null.	Passa se tutti i valori di riferimento sono 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 mutation. Per istruzione quando si utilizza DML.	Per transazione quando si utilizza l'API mutation. Per istruzione quando si utilizza DML.
Possono essere rimossi facilmente	No. L'interleaving delle tabelle non può essere rimosso dopo la creazione, a meno che non elimini l'intera tabella figlio.	Sì

Passaggi successivi

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

• Scopri di più sullo schema di informazioni.