Creazione e gestione di relazioni con le chiavi esterne

In questa pagina viene descritto come gestire le relazioni con le chiave esterna nel database.

Una chiave esterna è una colonna condivisa tra le tabelle per stabilire un collegamento tra i dati correlati. Quando utilizzi una chiave esterna, Spanner garantisce che questa relazione venga mantenuta.

Il seguente diagramma mostra uno schema di database di base in cui i dati di una tabella hanno una relazione con i dati di un'altra tabella.

Figura 1. Diagramma dello schema di un database di elaborazione degli ordini

Nello schema mostrato nella Figura 1 sono presenti tre tabelle:

• La tabella Customers registra i nomi di ogni cliente.
• La tabella Orders tiene traccia di tutti gli ordini effettuati.
• La tabella Products memorizza le informazioni sul prodotto per ogni prodotto.

Esistono due relazioni di chiave esterna tra queste tabelle:

• Viene definita una relazione di chiave esterna tra la tabella Orders e la tabella Customers per garantire che non sia possibile creare un ordine se non esiste un cliente corrispondente.

• Una relazione di chiave esterna tra la tabella Orders e la tabella Products garantisce che non sia possibile creare un ordine per un prodotto che non esiste.

Utilizzando lo schema precedente come esempio, questo argomento illustra le istruzioni CONSTRAINT del Data Definition Language (DDL) che puoi utilizzare per gestire le relazioni tra le tabelle di un database.

Per impostazione predefinita, tutte le chiavi esterne in Spanner sono chiavi esterne applicate, che applicano l'integrità referenziale. In Spanner puoi anche scegliere di utilizzare chiavi esterne informative, che non convalidano né applicano l'integrità referenziale. Per saperne di più, consulta Confronto delle chiavi esterne e Scegli il tipo di chiave esterna da utilizzare. Se non specificato, le chiavi esterne negli esempi di questa pagina sono chiavi esterne applicate.

Aggiungi una chiave esterna a una nuova tabella

Supponiamo che tu abbia creato una tabella Customers nel tuo database di base per gli ordini dei prodotti. Ora hai bisogno di una tabella Orders per archiviare le informazioni sugli ordini effettuati dai clienti. Per assicurarti che tutti gli ordini siano validi, non vuoi consentire al sistema di inserire righe nella tabella Orders, a meno che non esista anche una voce corrispondente nella tabella Customers. Pertanto, è necessaria una chiave esterna applicata per stabilire una relazione tra le due tabelle. Una scelta è aggiungere una colonna CustomerID alla nuova tabella e utilizzarla come chiave esterna per creare una relazione con la colonna CustomerID nella tabella Customers.

Quando crei una nuova tabella con una chiave esterna, utilizzi REFERENCE per stabilire una relazione con un'altra tabella. La tabella che contiene l'istruzione REFERENCE è chiamata tabella di riferimento. La tabella denominata nell'istruzione REFERENCE è la tabella di riferimento. La colonna denominata nell'istruzione REFERENCE è chiamata colonna di riferimento.

L'esempio seguente mostra come utilizzare l'istruzione DDL CREATE TABLE per creare la tabella Orders con un vincolo di chiave esterna che fa riferimento a CustomerID nella tabella Customers.

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)
);

L'istruzione precedente contiene una clausola CONSTRAINT con le seguenti caratteristiche:

• Utilizzo della sintassi CONSTRAINT per assegnare un nome a un vincolo, semplificando l'eliminazione della tabella utilizzando il nome scelto.

• Il vincolo ha il nome FK_CustomerOrder. I nomi dei vincoli hanno come ambito lo schema e devono essere univoci all'interno dello schema.

• La tabella Orders, su cui definisci il vincolo, è la tabella di riferimento. La tabella Customers è la tabella di riferimento.

• La colonna di riferimento nella tabella di riferimento è CustomerID. Fa riferimento al campo CustomerID nella tabella Customers. Se qualcuno tenta di inserire una riga in Orders con un CustomerID che non esiste in Customers, l'inserimento non va a buon fine.

Nota: spesso le colonne di riferimento della tabella di riferimento corrispondono alle colonne della chiave primaria, ma non è obbligatorio. Una tabella di riferimento può anche fare riferimento a colonne non primarie di una tabella di riferimento.

L'esempio seguente mostra un'istruzione di creazione di una tabella alternativa. In questo caso, il vincolo di chiave esterna è definito senza un nome. Quando utilizzi questa sintassi, Spanner genera un nome per te. Per scoprire i nomi di tutte le chiavi esterne, consulta Visualizza le proprietà di una relazione di chiave esterna.

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
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,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);

Aggiunta di una chiave esterna a una tabella esistente

Vuoi anche assicurarti che i clienti possano ordinare solo prodotti esistenti. Se la tabella ha vincoli esistenti, devi eliminare tutti i vincoli. In Spanner, tutti i vincoli applicati in una tabella devono essere implementati contemporaneamente in un'unica istruzione DDL batch.

Se la tabella non ha vincoli esistenti, puoi utilizzare l'istruzione DDL ALTER TABLE per aggiungere un vincolo di chiave esterna applicato alla tabella Orders esistente, come mostrato nell'esempio seguente:

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

La colonna di riferimento in Orders è ProductID e fa riferimento alla colonna ProductID in Products. Se non hai problemi a lasciare che sia Spanner a denominare questi vincoli, utilizza la seguente sintassi:

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Aggiungi una chiave esterna con un'azione di eliminazione a una nuova tabella

Ricorda l'esempio precedente in cui hai una tabella Customers in un database di ordini di prodotti che richiede una tabella Orders. Vuoi aggiungere un vincolo di chiave esterna che faccia riferimento alla tabella Customers. Tuttavia, vuoi assicurarti che quando elimini un record cliente in futuro, Spanner elimini anche tutti gli ordini per quel cliente. In questo caso, devi utilizzare l'azione ON DELETE CASCADE con il vincolo di chiave esterna.

La seguente istruzione DDL CREATE TABLE per la tabella Orders include il vincolo di chiave esterna che fa riferimento alla tabella Customers con un'azione ON DELETE CASCADE.

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);

L'istruzione precedente contiene un vincolo di chiave esterna con una clausola ON DELETE CASCADE. La colonna CustomerID è una chiave esterna che fa riferimento al campo CustomerID nella tabella Customers. Ciò significa che ogni valore CustomerID nella tabella Orders deve esistere anche nella tabella Customers. Se qualcuno tenta di eliminare una riga dalla tabella Customers, anche tutte le righe della tabella Orders che fanno riferimento al valore CustomerID eliminato vengono eliminate nella stessa transazione.

Analogamente, puoi utilizzare l'azione ON DELETE SET NULL per impostare le colonne di riferimento su NULL anziché eliminare le righe di riferimento. Per utilizzare ON DELETE SET NULL, le colonne di riferimento devono essere nullable (ovvero non contrassegnate come NOT NULL). Non possono far parte della chiave primaria o delle colonne generate della tabella di riferimento.

Per informazioni dettagliate sul confronto tra ON DELETE SET NULL e ON DELETE CASCADE in mutazioni, letture e comportamento a cascata, consulta Utilizzo delle risorse per le azioni di eliminazione chiave esterna esterne.

La seguente istruzione DDL CREATE TABLE per la tabella Orders include un vincolo di chiave esterna che fa riferimento alla tabella Customers con un'azione ON DELETE SET NULL. In questo esempio, CustomerID nella tabella Orders è nullable.

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL,
PRIMARY KEY (OrderID)
);

Nota: se non specifichi un'azione per la chiave esterna, l'azione predefinita è NO ACTION. Ciò significa che se qualcuno tenta di eliminare una riga dalla tabella Customers e nella tabella Orders sono presenti righe che fanno riferimento al valore CustomerID eliminato, l'operazione di eliminazione non va a buon fine e viene visualizzato un errore di violazione del vincolo di chiave esterna.

Aggiungi una chiave esterna con un'azione di eliminazione a una tabella

Vuoi anche assicurarti che gli ordini vengano creati solo per i prodotti esistenti. Puoi utilizzare ALTER TABLE per aggiungere un altro vincolo di chiave esterna con l'azione ON DELETE CASCADE alla tabella degli ordini nel seguente modo:

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

L'eliminazione di una riga dalla tabella Products comporta l'eliminazione di tutte le righe della tabella Orders che fanno riferimento al valore ProductID eliminato. Puoi anche utilizzare ON DELETE SET NULL in un'istruzione ALTER TABLE se la colonna di riferimento è nullable, non fa parte della chiave primaria e non è una colonna generata.

Nota: l'aggiunta o l'eliminazione di un'azione di chiave esterna su un vincolo di chiave esterna esistente non è supportata. Devi aggiungere un nuovo vincolo di chiave esterna con un'azione. Per saperne di più, consulta Modifiche dello schema a lunga esecuzione.

Utilizza chiavi esterne informative (solo GoogleSQL)

Le chiavi esterne informative consentono all'ottimizzatore delle query di utilizzare la relazione con la chiave esterna senza l'overhead derivante dai controlli di integrità referenziale eseguiti dalle chiavi esterne applicate. Le chiavi esterne informative sono utili quando l'applicazione di una rigorosa integrità referenziale è poco pratica o comporta un overhead significativo delle prestazioni.

Continuando con l'esempio precedente, immagina di voler modellare le relazioni tra le tabelle Customers, Orders e Products. Tuttavia, l'applicazione di una rigorosa integrità referenziale ai dati delle tabelle potrebbe introdurre colli di bottiglia delle prestazioni, soprattutto durante i periodi di picco degli acquisti con volumi di ordini elevati. Inoltre, i clienti potrebbero effettuare ordini di prodotti che non sono più disponibili e che sono stati rimossi dalla tabella Products.

Puoi creare la tabella Orders utilizzando chiavi esterne informative:

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,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

Creando una chiave esterna informativa con NOT ENFORCED, consenti la possibilità che un ordine faccia riferimento a un cliente o a un prodotto inesistente. L'utilizzo di una chiave esterna informativa anziché di un vincolo di chiave esterna applicato è una buona scelta se un account cliente potrebbe essere eliminato o un prodotto potrebbe non essere più disponibile. Con una chiave esterna informativa, Spanner non esegue la convalida dell'integrità referenziale. In questo modo, l'overhead di scrittura viene ridotto, migliorando potenzialmente le prestazioni durante i periodi di picco dell'elaborazione degli ordini.

Puoi consentire all'ottimizzatore delle query di utilizzare le relazioni per generare piani di query efficienti. In questo modo, puoi migliorare le prestazioni delle query che uniscono le tabelle nelle colonne delle chiave esterna. Per saperne di più, consulta Chiave esterna informativa per l'ottimizzazione delle query.

Attenzione: se la logica dell'applicazione richiede una rigorosa integrità referenziale (ad esempio, impedire ordini di prodotti che non esistono), devi implementare controlli aggiuntivi a livello di applicazione. I risultati delle query potrebbero essere influenzati se sono presenti incoerenze nei dati. Ad esempio, una query che unisce Orders con Products potrebbe restituire un ordine con un ProductID non più disponibile se l'ottimizzatore utilizza una chiave esterna informativa.

Esegui query sui dati tra relazioni con chiave esterna

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

Integrità referenziale con chiavi esterne applicate

Il motivo principale per aggiungere relazioni di chiave esterna applicate è consentire a Spanner di mantenere l'integrità referenziale dei dati. Se modifichi i dati in modo da violare un vincolo di chiave esterna, l'aggiornamento non va a buon fine e viene generato un errore.

Considera i dati nella Figura 2. Alcuni clienti hanno ordinato dei prodotti, come mostrato nella tabella Orders. A causa del vincolo di chiave esterna applicato, i dati inseriti nella tabella Orders hanno un'integrità referenziale.

Figura 2. Dati campionati per il nostro database degli ordini.

I seguenti esempi mostrano cosa succede quando provi a modificare i dati in un modo che comprometterebbe l'integrità referenziale.

• Aggiungi una riga alla tabella Orders con un valore CustomerID che non esiste in Customers

  Cosa succede se provi la seguente modifica, dati i dati di esempio del diagramma precedente?

  INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);
  

  In questo caso, il sistema tenta di inserire una riga in Orders con un CustomerID (447) che non esiste nella tabella Customers. Se il sistema lo facesse, avresti un ordine non valido nel tuo sistema. Tuttavia, con il vincolo di chiave esterna applicato che hai aggiunto alla tabella Orders, la tabella è protetta. INSERT non va a buon fine con il seguente messaggio, supponendo che il vincolo si chiami FK_CustomerOrder.

  Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`.
  Cannot find referenced values in Customers(CustomerID).
  

  A differenza delle chiavi esterne applicate, le chiavi esterne informative non applicano l'integrità referenziale. Se FK_CustomerOrder è una chiave esterna informativa, l'istruzione insert ha esito positivo perché Spanner non convalida l'esistenza del CustomerID corrispondente nella tabella Customers. Per questo motivo, i dati potrebbero non essere conformi all'integrità referenziale definita da FK_CustomerOrder.

• Tentativo di eliminare una riga dalla tabella Customers quando il cliente è indicato in un vincolo di chiave esterna applicato.

  Immagina una situazione in cui un cliente annulla l'iscrizione al tuo negozio online. Vuoi rimuovere il cliente dal tuo backend, quindi provi a eseguire la seguente operazione.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  In questo esempio, Spanner rileva tramite il vincolo della chiave esterna che nella tabella Orders sono ancora presenti record che fanno riferimento alla riga del cliente che stai tentando di eliminare. In questo caso viene visualizzato il seguente errore.

  Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

  Per risolvere il problema, elimina prima tutte le voci di riferimento in Orders. Puoi anche definire la chiave esterna con l'azione ON DELETE CASCADE per consentire a Spanner di gestire l'eliminazione delle voci di riferimento.

  Analogamente, se FK_CustomerOrder è una chiave esterna informativa, l'azione di eliminazione va a buon fine perché Spanner non garantisce l'integrità referenziale delle chiavi esterne informative.

Visualizzazione delle proprietà di una relazione di chiave esterna

INFORMATION_SCHEMA di Spanner contiene informazioni sulle chiavi esterne e sui relativi indici di supporto. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere eseguendo query su INFORMATION SCHEMA.

Per saperne di più sugli indici di supporto, consulta Indici di supporto delle chiavi esterne.

Quali vincoli sono definiti nel mio database?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

Quali chiavi esterne sono definite nel mio database?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Quali indici sono indici secondari per le chiavi esterne, noti anche come indici di supporto?

Gli indici di supporto delle chiavi esterne sono gestiti da Spanner , quindi l'esecuzione di query per SPANNER_IS_MANAGED nella vista INDEXES restituisce tutti gli indici di supporto.

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';

Qual è l'azione referenziale definita con il vincolo di chiave esterna?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Una chiave esterna è applicata o non applicata?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE, tc.ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';

Per saperne di più, consulta Information Schema.

Rimozione di una relazione di chiave esterna

Il seguente DDL elimina un vincolo di chiave esterna dalla tabella Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Gli indici di supporto della chiave esterna vengono eliminati automaticamente quando viene eliminato il vincolo stesso.

Supporto per relazioni di chiave esterna più complesse

I seguenti argomenti mostrano come utilizzare le chiavi esterne per applicare relazioni più complesse tra le tabelle.

Più colonne

Le chiavi esterne possono fare riferimento a più colonne. L'elenco di colonne forma una chiave che corrisponde alla chiave primaria di una tabella o a un indice di supporto. La tabella di riferimento contiene le chiavi esterne della chiave della tabella di riferimento.

Nell'esempio seguente, le definizioni di chiave esterna applicate indicano che:

• Ogni valore SongName nella tabella TopHits deve avere un valore corrispondente nella tabella Songs.

• Ogni coppia di valori SingerFirstName e SingerLastName deve avere una coppia di valori FirstName e LastName corrispondente nella tabella Singers.

GoogleSQL

CREATE TABLE TopHits (
Rank INT64 NOT NULL,
SongName STRING(MAX),
SingerFirstName STRING(MAX),
SingerLastName STRING(MAX),

-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),

-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

PostgreSQL

CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,

-- Song names must either be NULL or have matching values in Songs.
FOREIGN KEY (SongName) REFERENCES Songs (SongName),

-- Singer names must either be NULL or have matching values in Singers.
FOREIGN KEY (SingerFirstName, SingerLastName)
REFERENCES Singers (FirstName, LastName),

PRIMARY KEY (Rank)
);

Riferimenti circolari

A volte le tabelle presentano dipendenze circolari, forse per motivi legati ai sistemi legacy o alla denormalizzazione. Le chiavi esterne di Spanner consentono riferimenti circolari. Poiché una tabella di riferimento deve esistere prima che una chiave esterna possa farvi riferimento, una delle chiavi esterne deve essere aggiunta con un'istruzione ALTER TABLE. Ecco un esempio

1. Crea TableA, senza una chiave esterna.
2. Crea TableB con un vincolo di chiave esterna su TableA.
3. Utilizza ALTER TABLE su TableA per creare un riferimento di chiave esterna a TableB.

Tabelle con autoriferimento

Un tipo speciale di riferimento circolare è una tabella che definisce una chiave esterna che fa riferimento alla stessa tabella. Ad esempio, il seguente snippet mostra una chiave esterna per applicare la regola secondo cui il ManagerId di un dipendente deve essere anche un dipendente.

GoogleSQL

CREATE TABLE Employees (
EmployeeId INT64 NOT NULL,
EmployeeName STRING(MAX) NOT NULL,
ManagerId INT64,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

PostgreSQL

CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);

Passaggi successivi

• Scopri di più sul supporto delle chiavi esterne in Spanner.

• Scopri di più su INFORMATION SCHEMA di Spanner.