Crea y administra relaciones de claves externas

En esta página, se describe cómo administrar las relaciones de claves externas en la base de datos.

Las claves externas te permiten definir relaciones entre tablas. Cloud Spanner garantiza la integridad referencial entre estas tablas. En el siguiente diagrama, se ilustra un esquema de base de datos simple que usaremos en esta guía.

Ejemplo de esquema de base de datos que muestra relaciones de claves externas entre tablas.

Figura 1. Diagrama de un esquema de base de datos de procesamiento de pedidos

Hay tres tablas en el esquema que se muestra en la figura 1:

  • En la tabla Customers, se registran los nombres de cada cliente.
  • En las tablas Orders, se realiza un seguimiento de todos los pedidos realizados.
  • En la tabla Products, se almacena la información de cada producto.

Existen dos relaciones de claves externas entre estas tablas:

  • Una relación de clave externa se define entre la tabla Orders y la tabla Customers para garantizar que no se pueda crear un pedido a menos que haya un cliente correspondiente.

  • Una relación de clave externa entre la tabla Orders y la Products garantiza que no se pueda crear un pedido para un producto que no existe.

Con este esquema como referencia, veamos las instrucciones del lenguaje de definición de datos (DDL) que puedes usar para administrar estas restricciones en tu base de datos.

Agrega una clave externa durante la creación de una tabla nueva

Supongamos que ya creamos la tabla Customers en nuestra base de datos simple de pedido de producto. Necesitamos una tabla Orders para almacenar información sobre los pedidos que realizan los clientes. Para garantizar que todos los pedidos sean válidos, no queremos permitir que el sistema inserte filas en la tabla de pedidos que no tienen una entrada que coincida en nuestra tabla Customers.

Esta es la declaración de DDL CREATE TABLE para la tabla Orders que incluye la restricción de clave externa que hace referencia a la tabla Customers.

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

La declaración anterior contiene una cláusula CONSTRAINT que podemos describir de la siguiente manera:

  • El uso de la sintaxis CONSTRAINT te permite nombrar una restricción, lo que facilita la eliminación mediante el nombre que elegiste.

  • La restricción tiene el nombre FK_CustomerOrder. Los nombres de las restricciones se limitan al esquema y deben ser únicos dentro del esquema.

  • La tabla Orders, en la que definimos la restricción, se denomina tabla referente. La tabla Customers es la tabla a la que se hace referencia.

  • La columna referencia en la tabla de referencias es CustomerID. Hace referencia al campo CustomerID en la tabla Customers. Si alguien intenta insertar una fila en Orders con un CustomerID que no existe en Customers, la inserción fallará.

En el siguiente fragmento, se muestra una instrucción de creación de tabla alternativa. Aquí, la restricción de clave externa se define sin un nombre. Cuando usas esta sintaxis, Cloud Spanner genera un nombre. Para descubrir los nombres de todas las claves externas, consulta Visualiza las propiedades de una relación de clave externa.

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

Agrega una clave externa a una tabla existente

También queremos asegurarnos de que los pedidos solo se puedan crear para los productos que existen. Usaremos ALTER TABLE para agregar otra restricción de clave externa a la tabla de pedidos:

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

La columna referente en Orders es ProductID y hace referencia a la columna ProductID en Products. Si deseas usar Cloud Spanner para nombrar estas restricciones, usa la siguiente sintaxis:

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

Consulta datos en relaciones de clave externa

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

Ejemplo de integridad referencial

La razón principal para agregar relaciones de claves externas es que Cloud Spanner puede mantener la integridad referencial de tus datos. Si modificas los datos de una manera que rompe una restricción de clave externa, la actualización fallará y mostrará un error.

Considera los datos de la Figura 2 que aparece a continuación. Algunos clientes pidieron productos, como se muestra en la tabla de pedidos. Debido a las claves externas, podemos garantizar que los datos que se insertaron en la tabla Orders tengan integridad referencial.

Datos de muestra para las tablas Clientes, Productos y Pedidos.

Figura 2. Datos de muestra para la base de datos de pedidos.

Veamos qué sucede cuando intentamos modificar los datos de una manera que rompa la integridad referencial.

Agrega una fila a la tabla Orders con un valor CustomerID que no existe en Customers

¿Qué sucede si probamos la siguiente modificación, según los datos de muestra del diagrama anterior?

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

En este caso, intentamos insertar una fila en Orders con un CustomerID (447) que no existe en la tabla Customers. Si lo permitiéramos, tendríamos un pedido no válido en nuestro sistema. Sin embargo, la restricción de clave externa que definimos de la tabla Orders a la tabla Customers nos protege y el INSERT falla con el siguiente mensaje, siempre que la restricción sea llamada FK_CustomerOrder.

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

Se intenta borrar una fila de la tabla Customers cuando se hace referencia al cliente en una restricción de clave externa.

Imagina una situación en la que un cliente anula su suscripción a nuestra tienda en línea. Queremos quitar al cliente de nuestro backend, de modo que realizamos la siguiente operación.

DELETE FROM Customers WHERE CustomerID = 721;

En este ejemplo, Cloud Spanner detecta mediante la restricción de clave externa que todavía hay registros en la tabla Orders que hacen referencia a la fila del cliente que intentamos borrar. En este caso, se muestra el siguiente error.

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

Para solucionar este problema, primero borraremos todas las entradas de referencia de Orders. Otra solución, si definimos el CustomerID como NULLABLE, sería establecer el campo CustomerID en NULL para quitar la referencia. CASCADE DELETE no es compatible con claves externas.

Visualiza las propiedades de una relación de clave externa

INFORMATION_SCHEMA de Cloud Spanner contiene información sobre claves externas y sus índices de respaldo. Estos son algunos ejemplos de las preguntas que puedes responder si consultas INFORMATION SCHEMA.

Para obtener más información sobre los índices de respaldo, consulta Índices de respaldo de claves externas.

¿Qué restricciones se definen en la base de datos?

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

¿Qué claves externas se definen en la base de datos?

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

¿Qué índices son secundarios (de respaldo) para las claves externas?

Cloud Spanner administra los índices de copia de seguridad de claves externas, por lo que consultar SPANNER_IS_MANAGED en la vista INDEXES mostrará todos los índices de copia de seguridad.

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 is true;

Para obtener más información, consulta Esquema de información.

Quita una relación de clave externa

Con la siguiente DDL, se descarta una restricción de clave externa de la tabla Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Los índices de respaldo de claves externas se descartan automáticamente cuando se descarta la restricción.

Compatibilidad con relaciones más complejas de claves externas

Varias columnas

Las claves externas pueden hacer referencia a varias columnas. La lista de columnas forma una clave que corresponde a la clave primaria de una tabla o a un índice de respaldo. La tabla de referencia contiene claves externas de la clave de tabla a la que se hace referencia.

En el siguiente ejemplo, las definiciones de clave externa indican a Cloud Spanner que cada valor SongName en la tabla TopHits debe tener un valor coincidente en la tabla Canciones; cada par de valores SingerFirstName y SingerLastName debe tener un par de valores FirstName y LastName coincidentes en la tabla Cantantes.

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

Referencias circulares

A veces, las tablas tienen dependencias circulares, quizás por razones heredadas o debido a la desnormalización. Las claves externas de Cloud Spanner permiten referencias circulares. Debido a que una tabla a la que se hace referencia debe existir antes de que una clave externa pueda hacer referencia a ella, una de las claves externas debe agregarse con una declaración ALTER TABLE. Por ejemplo:

  1. Crear TablaA, sin una clave externa
  2. Cree una TablaB con una restricción de clave externa en la TablaA.
  3. Usa ALTER TABLE en TablaA para crear una referencia de clave externa a TablaB.

Tablas que hacen referencia a sí mismas

Un tipo especial de referencia circular es una tabla que define una clave externa que hace referencia a la misma tabla. Por ejemplo, en el siguiente fragmento, se muestra una clave externa para hacer cumplir que ManagerId de un empleado también es un empleado.

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

Qué sigue