Chiavi esterne

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

Panoramica

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

Immagina di essere lo sviluppatore principale di un'attività di e-commerce. Stai progettando un database per elaborare gli ordini dei clienti. Il database deve memorizzare informazioni su ogni ordine, cliente e prodotto. La figura 1 illustra il database di base la struttura dell'applicazione.

Figura 1. Diagramma di un database di elaborazione degli ordini

Definisci una tabella Customers per archiviare 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 collegamenti tra le tabelle mappate al seguente relazioni con il mondo:

• Un cliente ha effettuato un ordine

• È stato effettuato un ordine per un prodotto

Sei tu a decidere se il tuo database deve applicare le seguenti regole per garantire la validità degli ordini nel nostro sistema.

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

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

Quando applichiamo queste regole o limitazioni, diciamo che stiamo mantenendo l'integrità referenziale dei nostri 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 andranno a buon fine. L'integrità referenziale impedisce errori da parte dell'utente. Spanner applica l'integrità referenziale tramite chiavi esterne.

Forza l'integrità referenziale con chiavi esterne

Esaminiamo di nuovo l'esempio di elaborazione degli ordini, con l'aggiunta di ulteriori dettagli al design, come mostrato nella Figura 2.

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

Il design ora mostra i nomi e i tipi di colonna in ogni tabella. Tabella Orders definisce anche due relazioni di chiave esterna. FK_CustomerOrder garantisce che 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 la seguente tabella associa questi vincoli alle regole del mondo reale che in modo forzato.

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

Spanner non riesce in nessuna 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 di seguito.

Definire le chiavi esterne

Le chiavi esterne vengono create e rimosse dal database Spanner utilizzando DDL. Le chiavi esterne vengono aggiunte a una nuova tabella con l'istruzione CREATE TABLE. Analogamente, puoi aggiungere o rimuovere una chiave esterna 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 con le chiavi esterne.

Nota: puoi anche aggiungere e rimuovere 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 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 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 precedente, puoi assegnare un nome a ogni vincolo di chiave esterna. Se non specifichi un nome, Spanner lo genera automaticamente. Il nome generato è disponibile per le query da INFORMATION_SCHEMA di Spanner. I nomi delle limitazioni hanno come ambito lo schema, insieme ai nomi di tabelle e indici, ad esempio, e devono essere univoci all'interno dello schema.

• Il numero di colonne di riferimento deve essere lo stesso. L'ordine è significativo. La prima colonna di riferimento si riferisce alla prima colonna di riferimento, la seconda alla seconda ecc.

• Una colonna di riferimento e la rispettiva controparte a cui viene fatto riferimento devono essere dello stesso tipo. Anche le colonne devono essere indicizzabili.

• Non è possibile 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 "con riferimento autonomo"). Un esempio è una tabella Dipendenti 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 di riferimento deve esistere prima di creare una chiave esterna, quindi almeno una delle chiavi esterne deve essere aggiunto utilizzando l'istruzione ALTER TABLE.

• Le chiavi a cui viene fatto riferimento devono essere univoche. Spanner utilizza il 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 potrebbe anche essere in grado di usare la chiave primaria del riferimento tabella, sebbene sia meno comune. In caso contrario, Spanner crea NULL_FILTERED INDEX nelle colonne di riferimento.

• Le chiavi esterne non utilizzano gli indici secondari che hai creato. creano la propria propri indici di supporto. Questi indici di supporto sono utilizzabili nelle valutazioni delle query, incluse le direttive force_index esplicite. Il nome degli indici di supporto sono interrogabili da INFORMATION_SCHEMA di Spanner. Per maggiori informazioni per ulteriori informazioni, consulta Indici di backup.

Azioni delle chiavi esterne

Le azioni delle chiavi esterne ti consentono di controllare cosa succede alla colonna vincolata quando la colonna a cui fa riferimento viene eliminata o aggiornata. Spanner supporta l'uso dell'azione ON DELETE CASCADE. Con 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 la CREATE TABLE per aggiungere chiavi esterne con un'azione a una nuova tabella. Analogamente, puoi utilizzare l'istruzione ALTER TABLE per aggiungere un'azione della 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 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 che hanno un'azione.

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

Modifiche allo schema a lungo termine

Aggiunta di una chiave esterna a una tabella esistente o creazione di una nuova tabella con un chiave esterna, può portare a operazioni a lunga esecuzione. Nel caso di una nuova tabella, la tabella non può essere modificata fino al completamento dell'operazione a lunga esecuzione.

Per una nuova tabella con una chiave esterna, Spanner deve eseguire il completamento dei dati gli indici a cui si fa riferimento in base alle esigenze di ogni chiave esterna.

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

L'aggiunta di un'azione di chiave esterna a un vincolo esistente non è supportata. Ti consigliamo di procedere come segue:

1. Aggiungi una nuova limitazione con azione.
2. Elimina il vincolo precedente senza alcuna azione.

In questo modo si evita un problema di Long-running Alter Constraint Operation. Dopo la creazione la nuova chiave esterna con l'azione ON DELETE CASCADE, l'effetto netto sia è ELIMINA CASCATA. L'eliminazione di un vincolo potrebbe comportare l'eliminazione del indici di supporto di chiave esterna se gli indici non sono utilizzati in un'altra chiave esterna i vincoli. In un secondo momento, se l'utente aggiunge la stessa limitazione della chiave esterna con azione, potrebbero essere necessarie operazioni di lunga durata, tra cui il backfill degli indici, la convalida delle limitazioni degli indici univoci e la convalida delle limitazioni referenziali delle chiavi esterne.

Nota: se non elimini il vincolo della chiave esterna precedente, c'è un overhead dovuto per convalidare i vincoli aggiuntivi di chiave esterna.

Entrambe le modifiche allo schema riportate sopra possono non riuscire se l'indice di riferimento non può essere creato a causa di una violazione del vincolo UNIQUE.

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

Convalida del vincolo per una transazione

Spanner convalida i vincoli di chiave esterna mentre è in corso una transazione o quando gli effetti delle scritture vengono resi visibili ai operazioni nella transazione.

Un valore inserito nelle colonne di riferimento viene abbinato ai valori della tabella e delle colonne di riferimento. Righe con NULL che fanno riferimento a valori non sono selezionati, ovvero puoi aggiungerli alla tabella di riferimento.

Spanner convalida tutte le limitazioni referenziali delle chiavi esterne applicabili quando si tenta di aggiornare i dati tramite istruzioni DML o un'API. Tutte in attesa viene eseguito il rollback delle modifiche se alcuni vincoli non sono validi.

La convalida avviene immediatamente dopo ogni istruzione DML. Ad esempio, devi inserire la riga di riferimento prima di inserire le relative righe di riferimento. Quando si utilizza un'API di mutazione, le mutazioni vengono messe in coda fino al commit della transazione. La convalida delle chiavi esterne viene posticipata fino al commit della transazione. In questo è consentito inserire prima le righe di riferimento.

Per ogni transazione vengono valutate le modifiche che influiscono sulle limitazioni delle chiavi esterne. 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 indici creati dall'utente. Creano il proprio propri indici di supporto.

Spanner può creare fino a due indici di supporto secondari per ogni chiave estranea, 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 nella tabella a cui fa riferimento in genere non è necessario.

L'indice di supporto per la tabella a cui viene fatto riferimento è UNIQUE NULL_FILTERED index; la creazione della chiave esterna non va a buon fine se i dati esistenti violano le vincolo di univocità dell'indice. L'indice di supporto per la tabella di riferimento è NULL_FILTERED.

Se due o più chiavi esterne richiedono lo stesso indice di supporto, Spanner creerà un unico indice per tutti. Gli indici di supporto vengono eliminati quando le chiavi esterne che le utilizzano vengono eliminate. Gli utenti non possono modificare o eliminare di backup.

Spanner utilizza lo schema di informazioni di ogni database per archiviare metadati sugli indici di supporto. Righe all'interno di INFORMATION_SCHEMA.INDEXES con un valore SPANNER_IS_MANAGED di true per descrivere gli indici di supporto.

Al di fuori delle query SQL che richiamano direttamente lo schema delle informazioni, La console Google Cloud non mostra altrimenti informazioni sugli indici di supporto di un database.

Azione a cascata di eliminazione a lunga durata

Quando elimini una riga da una tabella a cui viene fatto riferimento, Spanner deve eliminare tutte le righe nelle tabelle di riferimento che fanno riferimento alla riga eliminata. Questo può portare in un effetto a cascata, dove una singola operazione di eliminazione può generare migliaia di altre operazioni di eliminazione. Aggiunta di un vincolo di chiave esterna con eliminazione a cascata su una tabella o creare una tabella con vincoli di chiave esterna con eliminazione l'azione a cascata può rallentare le operazioni di eliminazione.

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

L'eliminazione di un numero elevato di record mediante una procedura di eliminazione a cascata di una chiave esterna impatto sulle prestazioni. Il motivo è che ogni record eliminato attiva l'eliminazione di tutti i record ad esso correlati che utilizzano chiave. Se il numero di mutazioni in una transazione supera 80.000, la transazione non va a buon fine.

Se devi eliminare un numero elevato di record mediante il metodo di eliminazione della chiave esterna a cascata, devi eliminare esplicitamente le righe dalle tabelle figlio prima eliminando la riga dalle tabelle padre. In questo modo, la transazione non fallisce a causa del limite di mutazioni.

Confronto di chiavi esterne e interfoliazione delle tabelle

L'interfoliazione delle tabelle di Spanner è una buona scelta per molti in cui la chiave primaria della tabella figlio include i dati e colonne di chiave primaria. La colocation delle righe figlio con le righe padre può migliorare significativamente le prestazioni.

Le chiavi esterne sono una soluzione padre-figlio più generica e sono adatte a ulteriori casi d'uso. Non si limitano alle colonne di chiave primaria e le tabelle possono avere più relazioni di chiave esterna, sia come genitore in alcune relazioni che come figlio altri. Tuttavia, una relazione di chiave esterna non implica la colocation delle tabelle nel livello di archiviazione.

Vediamo un esempio utilizzando lo schema di elaborazione degli ordini discusso in precedenza in questo argomento. Ricorda che la nostra tabella Orders è stata definita come segue:

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

Il design nella Figura 3 presenta alcune limitazioni. Ad esempio, ogni ordine può al momento contengono solo un articolo dell'ordine.

Immaginiamo che i nostri clienti ci dicano di voler ordinare più di un prodotto per ordine. Possiamo migliorare il nostro design introducendo una tabella OrderItems contenente una voce per ogni prodotto ordinato dal cliente. Possiamo introdurre un'altra chiave esterna per rappresentare questo nuovo rapporto uno a molti tra Orders e OrderItems. Tuttavia, sappiamo anche che molte il tempo necessario per eseguire query sugli ordini e i rispettivi ordini elementi. La colocation di questi dati migliorerebbe il rendimento, quindi creeremo relazione padre-figlio utilizzando la funzionalità di interfoliazione delle tabelle di Spanner.

Ecco come definiamo la tabella OrderItems, 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, possiamo 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 è superiore a 80.000, la transazione non va a buon fine. Queste eliminazioni con propagazione 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 di chiave esterna e devi eliminare un un numero elevato di righe, devi eliminarle esplicitamente dall'account secondario tabelle.

Se hai una tabella utente con una relazione di chiave esterna con un'altra tabella e l'eliminazione di una riga dalla tabella a cui si fa riferimento attiva l'eliminazione di milioni di righe, devi progettare lo schema con un'azione di eliminazione a cascata con "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 è rielaborato 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 le differenze tra le chiavi esterne e l'interfoliazione 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ì
È possibile utilizzare colonne non a chiave primaria	No	Sì
Numero di genitori supportati	0 ... 1	0 .. N
Archivia i dati principali e secondari	Sì	No
Supporta l'eliminazione 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.	Viene superato se un valore di riferimento è nullo. Viene superato se tutti i valori di riferimento sono diversi da null e la tabella di riferimento ha una riga con valori uguali a quelli di riferimento. Non riesce se non è stata trovata alcuna riga corrispondente.
Tempi di applicazione	Per operazione quando si utilizza l'API Mutazione. Per istruzione quando utilizzi DML.	Per transazione quando si utilizza l'API di mutazione. Per istruzione quando utilizzi DML.
Possono essere rimossi facilmente	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ù su come creare e gestire relazioni di chiave esterna.

• Scopri di più sullo schema delle informazioni.