Creazione e gestione di relazioni con le chiavi esterne

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

Una chiave esterna è una colonna condivisa tra tabelle per stabilire collegamento tra dati correlati. Quando usi una chiave esterna, Spanner assicura che questa relazione venga mantenuta.

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

Figura 1. Diagramma di uno schema di database di elaborazione degli ordini

Nello schema mostrato nella Figura 1 sono presenti tre tabelle:

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

Tra queste tabelle esistono due relazioni di chiave esterna:

• Viene definita una relazione di chiave esterna tra la tabella Orders e Customers per garantire che non sia possibile creare un ordine a meno che non sia presente 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 come esempio lo schema precedente, in questo argomento vengono trattati i Istruzioni CONSTRAINT Definition Language (DDL) utilizzabili per gestire relazioni tra le tabelle in un database.

Aggiunta di una chiave esterna durante la creazione di una nuova tabella

Supponi di aver creato una tabella Customers nel prodotto semplice un database di ordinamento. Ora hai bisogno di una tabella Orders per archiviare le informazioni sull'elemento gli ordini effettuati dai clienti. Per assicurarti che tutti gli ordini siano validi, non vuoi consenti al sistema di inserire righe nella tabella Orders, a meno che non sia presente anche una voce corrispondente nella tabella Customers. È quindi necessaria una chiave esterna stabilire una relazione tra le due tabelle. Un'opzione consiste nell'aggiungere colonna CustomerID alla nuova tabella e usala come chiave esterna per creare un 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 è denominata tabella di riferimento. La indicata nell'istruzione REFERENCE è la tabella riferita. La colonna denominato nell'istruzione REFERENCE è denominata colonna referencing (riferimento).

L'esempio seguente mostra come utilizzare l'istruzione DDL CREATE TABLE per crea la tabella Orders con un vincolo di chiave esterna che fa riferimento 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 che ha le seguenti caratteristiche: caratteristiche:

• Utilizzo della sintassi CONSTRAINT per assegnare un nome a un vincolo, in modo che venga è più facile rilasciare la tabella utilizzando il nome scelto.

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

• La tabella Orders, in base alla quale definisci il vincolo, è tabella di riferimento. La tabella Customers è la tabella di riferimento.

• La colonna dei riferimenti nella tabella dei riferimenti è CustomerID. it fa riferimento al campo CustomerID nella tabella Customers. Se qualcuno prova per inserire una riga in Orders con un CustomerID che non esiste in Customers, l'inserimento non riesce.

Nota: spesso le colonne di riferimento della tabella referenziata corrispondono principale, ma non è obbligatorio. Una tabella di riferimento può anche fare riferimento a colonne non principali di una tabella di riferimento.

L'esempio seguente mostra un'istruzione di creazione di tabelle alternativa. In questo caso, di chiave esterna è definito senza nome. Quando utilizzi questa sintassi, Spanner genera un nome per te. Per scoprire i nomi di tutte le persone straniere consulta la sezione Visualizzare le proprietà di una chiave esterna di IA generativa.

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

Devi inoltre assicurarti che i clienti possano ordinare solo prodotti che esistono. Innanzitutto, devi rilasciare il vincolo esistente. Quindi puoi utilizzare ALTER TABLE per aggiungere un altro vincolo di chiave esterna alla tabella Orders, come mostrato di seguito:

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

La colonna di riferimento in Orders è ProductID e fa riferimento al ProductID in Prodotti. Ancora una volta, se ti va bene che Spanner denomina questi utilizzare la seguente sintassi:

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

Aggiungere una chiave esterna con un'azione di eliminazione durante la creazione di una nuova tabella

Ricorda l'esempio precedente in cui in un prodotto è presente una tabella Customers che richiede una tabella Orders. Vuoi aggiungere una chiave esterna vincolo che fa riferimento alla tabella Customers. Tuttavia, devi assicurarti quando in futuro eliminerai il record di un cliente, Spanner elimina anche tutti gli ordini per quel cliente. In questo caso, vuoi utilizzare Azione ON DELETE CASCADE con il vincolo di chiave esterna.

La seguente istruzione DDL CREATE TABLE per la tabella Orders include il parametro 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 un ON DELETE CASCADE. La colonna CustomerID è una chiave esterna che fa riferimento al campo CustomerID nella tabella Customers. Ciò significa che ogni Il valore CustomerID nella tabella Orders deve esistere anche in Customers . Se un utente tenta di eliminare una riga dalla tabella Customers, le righe nella tabella Orders che fanno riferimento al valore CustomerID eliminato sono eliminati nella stessa transazione.

Nota: se non specifichi un'azione di chiave esterna, l'azione predefinita è NO ACTION. Ciò significa che se qualcuno cerca di eliminare una riga dal Customers e ci sono righe nella tabella Orders che fanno riferimento il valore CustomerID eliminato, l'operazione di eliminazione non va a buon fine a causa di una chiave esterna un errore di violazione del vincolo.

Aggiungere una chiave esterna con un'azione di eliminazione a una tabella esistente

Devi inoltre assicurarti che gli ordini vengano creati solo per i prodotti che esistono. Puoi utilizzare ALTER TABLE per aggiungere un altro vincolo di chiave esterna con ON DELETE CASCADE alla tabella degli ordini come segue:

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

L'eliminazione di una riga dalla tabella Products elimina tutte le righe nella Tabella Orders che fa riferimento al valore ProductID eliminato.

Nota: aggiunta o eliminazione di un'azione di chiave esterna su una chiave esterna esistente non è supportato. Devi aggiungere un nuovo vincolo di chiave esterna con un'azione. Per saperne di più, consulta Modifiche allo schema di lunga durata.

Query sui dati tra relazioni di chiave esterna

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

Esempi di come Spanner mantiene l'integrità referenziale

Il motivo principale per l'aggiunta di relazioni di chiave esterna è che Spanner possa mantenere integrità referenziale dell' i tuoi dati. Se modifichi i dati in modo da interrompere un vincolo di chiave esterna, l'aggiornamento non riesce e restituisce un errore.

Considera i dati nella Figura 2. Alcuni clienti hanno ordinato prodotti, ad esempio visualizzata nella tabella degli ordini. A causa delle chiavi esterne presenti possiamo garantire che i dati inseriti nella tabella Orders siano e l'integrità referenziale.

Figura 2. Dati di esempio per nel nostro database degli ordini.

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

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

  Che cosa succede se provi la seguente modifica, sulla base dei dati campione nel diagramma precedente?

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

  In questo caso, il sistema tenterà di inserire una riga in Orders con una CustomerID (447) che non esiste nella tabella Customers. Se il sistema questa operazione, l'ordine inserito nel sistema non è valido. Tuttavia, con vincolo di chiave esterna che hai aggiunto alla tabella Orders, la tabella è protette. L'errore INSERT ha esito negativo con il seguente messaggio, a condizione che il vincolo FK_CustomerOrder.

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

• Tenta di eliminare una riga dalla tabella Customers quando il cliente a cui viene fatto riferimento in un vincolo di chiave esterna.

  Immagina una situazione in cui un cliente annulla l'iscrizione al tuo negozio online. Vuoi rimuovere il cliente dal tuo backend, quindi prova a eseguire queste operazioni: operativa.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  In questo esempio, Spanner rileva tramite il vincolo di chiave esterna che nella tabella Orders siano ancora presenti record che fanno riferimento al cliente riga 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, devi prima eliminare tutte le voci di riferimento in Orders. Tu puoi anche definire la chiave esterna con l'azione ON DELETE CASCADE per Spanner gestisce l'eliminazione delle voci di riferimento.

Visualizzazione delle proprietà di una relazione di chiave esterna

INFORMATION_SCHEMA di Spanner contiene informazioni sulle chiavi esterne e i rispettivi indici di supporto. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere lo SCHEMA INFORMAZIONI.

Per saperne di più sugli indici di supporto, consulta Indici di supporto di 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 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 le 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;

Per saperne di più, consulta la sezione Schema delle informazioni.

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 il vincolo stesso viene eliminato.

Supporto per relazioni di chiave esterna più complesse

Più colonne

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

Nell'esempio seguente, le definizioni di chiave esterna indicano a Spanner che ogni valore SongName nella tabella TopHits deve avere un valore corrispondente in la tabella Brani; e ogni coppia di valori SingerFirstName e SingerLastName devono avere una coppia di valori FirstName e LastName corrispondente nella sezione Cantanti .

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 hanno dipendenze circolari, forse per motivi legacy o a causa della denormalizzazione. Le chiavi esterne di Spanner consentono riferimenti circolari. Poiché una tabella di riferimento deve esistere prima che una chiave esterna possa farvi riferimento, uno dei le chiavi esterne devono essere aggiunte con un'istruzione ALTER TABLE. Ecco un esempio

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

Tabelle autoreferenziali

Un tipo speciale di riferimento circolare è una tabella che definisce una chiave esterna fa riferimento alla stessa tabella. Ad esempio, il seguente snippet mostra una chiave esterna per far sì che anche il ManagerId di un dipendente sia 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ù sullo SCHEMA DI INFORMAZIONI di Spanner.