Claves externas

En este tema, se describen las claves externas en Spanner y cómo puedes usarlas para aplicar la integridad referencial en tu solución de base de datos.

Descripción general

Las claves externas te permiten definir relaciones entre tablas. Spanner garantiza que se mantenga la integridad de los datos de estas relaciones.

Imagina que eres el desarrollador líder de una empresa de comercio electrónico. Estás diseñando una base de datos para procesar los pedidos de los clientes. La base de datos debe almacenar información sobre cada pedido, cliente y producto. En la Figura 1, se ilustra la estructura básica de la base de datos para la aplicación.

Figura 1. Diagrama de una base de datos de procesamiento de pedidos

Define una tabla Customers en la que se almacene la información del cliente, una tabla Orders para llevar a cabo un seguimiento de todos los pedidos realizados y una tabla Products en la que se almacene la información sobre cada producto que los clientes pueden pedir.

En la figura 1, también se muestran vínculos entre las tablas que se asignan a las siguientes relaciones del mundo real:

• Un cliente realizó un pedido.

• Se realizó un pedido de un producto.

Debes decidir que tu base de datos aplique las siguientes reglas para garantizar que los pedidos en nuestro sistema sean válidos.

• No puedes crear un pedido para un cliente que no existe.

• Un cliente no puede realizar un pedido de un producto que no ofreces.

Cuando se aplican estas reglas, o restricciones, se mantiene la integridad referencial de los datos. Si una base de datos mantiene la integridad referencial, fallarán todos los intentos de agregar datos no válidos, lo que generaría vínculos o referencias no válidos entre datos. La integridad referencial evita los errores del usuario. Spanner aplica la integridad referencial mediante claves externas.

Cómo aplicar la integridad referencial con claves externas

Veamos nuevamente nuestro ejemplo de procesamiento de pedidos, con más detalles agregados al diseño, como se muestra en la Figura 2.

Figura 2. Diagrama de nuestro esquema de base de datos con claves externas

El diseño ahora muestra nombres y tipos de columnas en cada tabla. La tabla Orders también define dos relaciones de claves externas. FK_CustomerOrder garantiza que todas las filas de Orders tengan un CustomerID válido. La clave externa FK_ProductOrder garantiza que todos los valores ProductID en la tabla Orders sean válidos. En la siguiente tabla, se muestran estas restricciones a las reglas reales que queremos aplicar.

Nombre de la clave externa	Restricción	Descripción real
FK_CustomerOrder	Garantiza que todas las filas de Orders tengan un CustomerID válido	Un cliente válido realizó un pedido.
FK_ProductOrder	Garantiza que todas las filas de Orders tengan un ProductID válido	Se realizó un pedido de un producto válido.

Spanner genera una falla en cualquier transacción que intente insertar o actualizar una fila en la tabla Orders que tenga un CustomerID o ProductID que no se pueda encontrar en las tablas Customers y Products. También genera fallas en las transacciones que intentan actualizar o borrar filas en las tablas Customers y Products, lo que invalidaría los ID de la tabla Orders. Para obtener más detalles sobre cómo Spanner valida las restricciones. Consulta Restricción de transacciones de la consola de Cloud a continuación.

Cómo definir claves externas

Las claves externas se crean y se quitan de tu base de datos de Spanner con DDL. Las claves externas se agregan a una tabla nueva con la declaración CREATE TABLE. Del mismo modo, puedes agregar una clave externa a una tabla existente con la sentencia ALTER TABLE o quitarla de ella. El siguiente es un ejemplo de cómo crear una tabla nueva con una clave externa.

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

Para obtener más ejemplos sobre cómo crear y administrar claves externas, consulta Crea y administra relaciones de claves externas.

Nota: También puedes agregar y quitar claves externas con la consola de Google Cloud. Selecciona Editar como texto en la interfaz de creación de tablas y, luego, ingresa el DDL para administrar las claves externas de tu tabla.

La siguiente es una lista de características de las claves externas en Spanner.

• La tabla que define la clave externa es la tabla referente y las columnas de clave externa son las columnas referentes.

• La clave externa hace referencia a las columnas a las que se hace referencia de la tabla a la que se hace referencia.

• Como en el ejemplo anterior, puedes asignar un nombre a cada restricción de clave externa. Si no especificas un nombre, Spanner genera uno automáticamente. El el nombre generado se puede consultar desde INFORMATION_SCHEMA de Spanner. El alcance de los nombres de las restricciones se aplica al esquema, junto con los nombres de las tablas y los índices, y deben ser únicos dentro del esquema.

• La cantidad de columnas de referencia y a las que se hace referencia debe ser la misma. El orden es importante. La primera columna de referencia hace referencia a la primera columna a la que se hace referencia, la segunda, a la segundo, etcétera.

• Una columna referente y su contraparte referenciada deben ser del mismo tipo. Las columnas también deben poder indexarse.

• Las claves externas no se pueden crear en columnas con la opción allow_commit_timestamp=true.

• No se admiten columnas de arrays.

• Las columnas JSON no son compatibles.

• Una clave externa puede hacer referencia a columnas de la misma tabla (una clave externa de “autoreferencia”). Un ejemplo es una tabla de empleados con una columna de ManagerId que hace referencia a la columna EmployeeId de la tabla.

• Las claves externas también pueden formar relaciones circulares entre tablas, en las que dos tablas hacen referencia una a otra directa o indirectamente. La tabla a la que se hace referencia debe existir antes de crear una clave externa, por lo que se debe agregar al menos una de las claves externas mediante la declaración ALTER TABLE.

• Las claves a las que se hace referencia deben ser únicas. Spanner usa el PRIMARY KEY de la tabla a la que se hace referencia si las columnas a las que se hace referencia en la clave externa coinciden con las columnas de la clave primaria de la tabla a la que se hace referencia. Si Spanner no puede usar el clave primaria de la tabla a la que se hace referencia, crea un UNIQUE NULL_FILTERED INDEX sobre las columnas a las que se hace referencia.

• Spanner también puede usar la clave primaria de la referencia en una tabla, aunque es menos común. De lo contrario, Spanner creará un NULL_FILTERED INDEX sobre las columnas de referencia.

• Las claves externas no usan índices secundarios que hayas creado; crean sus propios índices de respaldo. Estos índices de respaldo se pueden usar en las evaluaciones de consultas, incluidas las directivas force_index explícitas. El nombre de los índices de respaldo se pueden consultar desde INFORMATION_SCHEMA de Spanner. Para obtener más información, consulta Índices de respaldo.

Acciones de clave externa

Las acciones de clave externa te permiten controlar lo que sucede con la columna restringida cuando se borra o actualiza la columna a la que hace referencia. Spanner admite el uso de la acción ON DELETE CASCADE. Con la clave externa ACTIVADA DELETE CASCADE cuando borras una fila que contiene un valor externo al que se hace referencia. clave, todas las filas que hacen referencia a esa clave también se borran en la misma transacción.

Puedes agregar una clave externa con una acción cuando crees tu base de datos con DDL. Usa la sentencia CREATE TABLE para agregar claves externas con una acción a una tabla nueva. De manera similar, puedes usa la sentencia ALTER TABLE para agregar una acción de clave externa a una acción de clave externa tabla o para quitar una acción de clave externa. El siguiente es un ejemplo de cómo crear una tabla nueva con una acción de clave externa.

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

La siguiente es una lista de características de las acciones de claves externas en Spanner.

• Las acciones de clave externa son ON DELETE CASCADE o ON DELETE NO ACTION.

• Puedes consultar INFORMATION_SCHEMA para encontrar restricciones de claves externas que tengan una acción.

• No se admite agregar una acción de clave externa a una restricción de clave externa existente. Debes agregar una nueva restricción de clave externa con una acción.

Cambios de esquema de larga duración

Agregar una clave externa a una tabla existente o crear una tabla nueva con una clave externa puede generar operaciones de larga duración. En el caso de una tabla nueva, no se puede escribir en la tabla hasta que se complete la operación de larga duración.

Para una tabla nueva con una clave externa, Spanner necesita reabastecer los índices a los que se hace referencia según sea necesario para cada clave externa.

Para una tabla existente con una clave externa, Spanner debe reabastecer los índices y los índices a los que se hace referencia según sea necesario. Además, Spanner valida los datos existentes en la tabla para garantizar que cumplan con restricción de integridad referencial de la clave externa. El cambio del esquema fallará si algún dato no es válido.

No se admite agregar una acción de clave externa a una restricción existente. Te recomendamos que hagas lo siguiente:

1. Agrega una restricción nueva con acción.
2. Descarta la restricción anterior sin ninguna acción.

De esta manera, se evita un problema de Long-running Alter Constraint Operation. Después de crear la nueva clave externa con la acción ON DELETE CASCADE, el efecto neto de ambas es DELETE CASCADE. Si descartas una restricción, es posible que se descarten los índices de respaldo de claves externas si los índices no se usan en otras restricciones de claves externas. Más adelante, si el usuario agrega la misma restricción de clave externa con podría requerir operaciones de larga duración, como el reabastecimiento de índices, validación de restricciones de índices únicas y validación de referencias referenciales de clave externa restricciones.

Nota: Si no descartas la restricción de clave externa anterior, se generará una sobrecarga para validar las restricciones de clave externa adicional.

Cualquiera de los cambios de esquema anteriores puede fallar si el índice al que se hace referencia no se puede crear debido a una infracción de la restricción UNIQUE.

Puedes consultar NFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE para comprobar el estado de creación de la clave externa.

Validación de restricciones para una transacción

Spanner valida las restricciones de clave externa cuando se está realizando una transacción. confirmadas o cuando los efectos de las escrituras se muestren las operaciones en la transacción.

Un valor insertado en las columnas referentes coincide con los valores de la tabla y las columnas a las que se hace referencia. Las filas con valores de referencia NULL no están verificadas, es decir, puedes agregarlas a la tabla de referencia.

Spanner valida todas las restricciones referenciales de clave externa aplicables. cuando intenta actualizar datos a través de declaraciones DML o una API. Todos los cambios pendientes se revierten en caso de que alguna restricción no sea válida.

La validación ocurre de inmediato después de cada declaración DML. Por ejemplo, debes insertar la fila a la que se hace referencia antes de insertar sus filas referentes. Cuando se usa una API de mutación, las mutaciones se almacenan en búfer hasta que la transacción se confirma. La validación de clave externa se aplaza hasta que se confirma la transacción. En este caso, se permite insertar primero las filas que hacen referencia.

Cada transacción se evalúa en busca de modificaciones que afecten las restricciones de las claves externas. Estas evaluaciones pueden requerir solicitudes adicionales al servidor. Los índices de respaldo también requieren tiempo de procesamiento adicional para evaluar las modificaciones de transacción y mantener los índices. También se requiere almacenamiento adicional para cada índice.

Índices de respaldo

Las claves externas no usan índices creados por el usuario. Crean sus propios índices de respaldo.

Spanner puede crear hasta dos índices de respaldo secundarios para cada clave externa, uno para las columnas de referencia y otro para las columnas a las que se hace referencia. Sin embargo, una clave externa generalmente hace referencia a las claves primarias de la tabla a la que se hace referencia, por lo que el segundo índice de la tabla a la que hace referencia no suele ser necesario.

El índice de respaldo para la tabla a la que se hace referencia es un índice UNIQUE NULL_FILTERED. La creación de la clave externa fallará si algún dato existente infringe la restricción de unicidad del índice. El índice de respaldo para la tabla referente es NULL_FILTERED.

Si dos o más claves externas requieren el mismo índice de respaldo, Spanner creará un único índice para todos ellos. Los índices de respaldo se descartan cuando se descartan las claves externas que los usan. Los usuarios no pueden modificar ni descartar los índices de respaldo.

Spanner usa el esquema de información de cada base de datos para almacenar metadatos sobre los índices de copia de seguridad. Filas dentro de INFORMATION_SCHEMA.INDEXES con un valor SPANNER_IS_MANAGED de true describen los índices de respaldo.

Más allá de las consultas de SQL que invocan directamente el esquema de información, el La consola de Google Cloud no muestra información. sobre los índices de respaldo de una base de datos.

Acción de eliminación en cascada de larga duración

Cuando borras una fila de una tabla a la que se hace referencia, Spanner debe borrar todas las filas de las tablas de referencia que hacen referencia a la fila borrada. Esto puede provocar a un efecto en cascada, en el que una sola operación de eliminación puede dar como resultado miles de otras operaciones de eliminación. Agregar una restricción de clave externa con acción de eliminación en cascada a una tabla o crear una tabla con restricciones de clave externa con acción de eliminación en cascada puede ralentizar las operaciones de eliminación.

Se superó el límite de mutación para la eliminación en cascada de claves externas

Borrar una gran cantidad de registros con una clave externa puede borrar en cascada que impactan en el rendimiento. Esto se debe a que cada registro que se borra activa la eliminación de todos los registros relacionados con él que usan claves foráneas. Si la cantidad de mutaciones en una transacción supera las 80,000 falla la transacción.

Si necesitas borrar una gran cantidad de registros con una eliminación en cascada de claves foráneas, debes borrar de forma explícita las filas de las tablas secundarias antes de borrar las filas de las tablas superiores. Esto evita que la transacción falle debido al límite de mutación.

Comparación de las llaves externas y la intercalación de tablas

La intercalación de tablas de Spanner es una buena opción para muchas relaciones entre tablas principales y secundarias en las que la clave primaria de la tabla secundaria incluye las columnas de clave primaria de la tabla principal. La ubicación conjunta de las filas secundarias con sus filas principales puede mejorar significativamente el rendimiento.

Las claves externas son una solución de relación principal-secundaria más general y abordan casos prácticos adicionales. No están limitadas a las columnas de clave primaria, y las tablas pueden tener varias relaciones de clave exterior, tanto como un superior en algunas relaciones, y un elemento secundario en otras. Sin embargo, una relación de clave externa no implica la ubicación conjunta de las tablas en la capa de almacenamiento.

Veamos un ejemplo con el esquema de procesamiento de pedidos que analizamos antes en este tema. Recupera nuestra tabla Orders de la siguiente manera:

Figura 3. Diagrama de nuestro esquema de base de datos con claves externas

El diseño de la Figura 3 tiene algunas limitaciones. Por ejemplo, cada pedido solo puede contener un elemento de pedido.

Imagina que nuestros clientes nos dicen que les gustaría poder pedir más de un producto por pedido. Podemos mejorar nuestro diseño si presentamos una tabla OrderItems que contiene una entrada para cada producto que el cliente pidió. Podemos ingresar otra clave externa para representar esta nueva relación de uno a varios entre Orders y OrderItems. Sin embargo, también sabemos que muchas veces queremos ejecutar consultas en los pedidos y sus respectivos elementos de pedido. La ubicación conjunta de estos datos mejoraría el rendimiento, por lo que crearemos la relación superior y secundaria mediante la capacidad de intercalación de tablas de Spanner.

A continuación, te mostramos cómo definimos la tabla OrderItems, intercalada 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 es una representación visual del esquema de base de datos actualizado como resultado de la introducción de esta tabla nueva, OrderItems, intercalada con Orders. Aquí también puedes ver la relación de uno a varios entre esas dos tablas.

Figura 4. Adición de una tabla intercalada OrderItems.

En esta configuración, podemos tener varias entradas OrderItems en cada pedido, y las entradas de OrderItems para cada pedido se intercalan, de modo que se ubican junto con los pedidos. Intercalar físicamente Orders y OrderItems de esta manera puede mejorar el rendimiento, ya que unifica antes las tablas y te permite acceder a filas relacionadas, a la vez que minimiza los accesos al disco. Por ejemplo, Spanner puede realizar uniones según la clave primaria de manera local, lo que minimiza el acceso al disco y el tráfico de red.

Si la cantidad de mutaciones en una transacción supera las 80,000 falla la transacción. Estas eliminaciones en cascada grandes funcionan bien para las tablas con una relación "intercalada en la tabla superior", pero no para las tablas con una relación de clave externa. Si tienes una relación de clave externa y necesitas borrar una grandes cantidades de filas, debes borrar explícitamente las filas del elemento secundario tablas primero.

Si tienes una tabla de usuarios con una relación de clave externa a otra tabla y borrar una fila de la tabla a la que se hace referencia activa la eliminación de millones de filas, debes diseñar tu esquema con una acción de eliminación en cascada con “intercalada en superior”.

Nota: Google recomienda que elijas representar las relaciones entre tablas primarias y secundarias como tablas intercaladas o como claves externas, pero no de ambas formas. Utilizar ambos es redundantes y podrían consumir recursos de almacenamiento y de procesamiento adicionales para para verificar las restricciones y mantener los índices de copia de seguridad.

Tabla de comparación

La siguiente tabla resume las diferencias entre las claves externas y la intercalación de tablas. Puedes usar esta información a fin de decidir qué es lo mejor para tu diseño.

Tipo de relación primaria-secundaria	Intercalación de tablas	Claves externas
Puedes usar claves primarias	Sí	Sí
Puedes usar columnas de clave no primaria	No	Sí
Cantidad de tablas principales admitidas	0 .. 1	0 .. N
Almacena datos superiores y secundarios al mismo tiempo	Sí	No
Admite la eliminación en cascada	Sí	Sí
Modo de coincidencia nula	Pasa si todos los valores referentes no son distintos de los valores a los que se hace referencia. Los valores nulos no son distintos de los valores nulos. los valores nulos son distintos de los valores no nulos.	Pasa si los valores referentes son nulos. Pasa si todos los valores referentes son no nulos y la tabla a la que se hace referencia tiene una fila con valores equivalentes a los valores referentes. Falla si no se encontró ninguna fila coincidente.
Tiempo de aplicación	Por operación cuando se usa la API de mutación Por declaración cuando se usa DML.	Por transacción cuando se usa la API de mutación Por declaración cuando se usa DML.
Se puede quitar fácilmente	No. El intercalado de tablas no se puede quitar después de crearlo, a menos que quites la tabla secundaria completa.	Sí

¿Qué sigue?

• Aprende a crear y administrar relaciones de claves externas.

• Obtén más información sobre el esquema de información.