Claves externas

En este documento, se describen las claves externas en Spanner y cómo puedes usarlas para aplicar la integridad referencial en tu base de datos. Los siguientes temas te ayudan a obtener información sobre las claves externas y cómo usarlas:

Descripción general de las claves externas en Spanner

Las claves externas definen relaciones entre tablas. Puedes usar claves externas para asegurarte de que se mantenga la integridad de los datos de estas relaciones en Spanner.

Imagina que eres el desarrollador principal de una empresa de comercio electrónico. Estás diseñando una base de datos para procesar pedidos de 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.

Estructura básica de la base de datos de procesamiento de pedidos

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

Defines una tabla Customers para almacenar la información del cliente, una tabla Orders para hacer un seguimiento de todos los pedidos y una tabla Products para almacenar información sobre cada producto.

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 realiza un pedido.

  • Se realiza un pedido de un producto.

Decides que tu base de datos aplique las siguientes reglas para garantizar que los pedidos en tu 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 aplicas estas reglas o restricciones, mantienes la integridad referencial de tus datos. Cuando una base de datos mantiene la integridad referencial, fallan todos los intentos de agregar datos no válidos, lo que generaría vínculos o referencias no válidos entre los datos. La integridad referencial evita los errores del usuario. De forma predeterminada, Spanner usa claves externas para aplicar la integridad referencial.

Define la integridad referencial con claves externas

A continuación, se vuelve a examinar el ejemplo de procesamiento de pedidos, con más detalles agregados al diseño, como se muestra en la figura 2.

Esquema de la base de datos con claves externas

Figura 2. Diagrama de un 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 espera que todas las filas de Orders tengan un CustomerId válido. La clave externa FK_ProductOrder espera que todos los valores ProductId de la tabla Orders sean válidos. En la siguiente tabla, se asignan estas restricciones a las reglas del mundo real que deseas aplicar.

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

Spanner aplica las restricciones que se especifican con claves externas aplicadas. Esto significa que Spanner falla cualquier transacción que intente insertar o actualizar una fila en la tabla Orders que tenga un CustomerId o ProductId que no se encuentre en las tablas Customers y Products. También falla las transacciones que intentan actualizar o borrar filas en las tablas Customers y Products que invalidarían los IDs en la tabla Orders. Para obtener más detalles sobre cómo Spanner valida las restricciones, consulta la sección Validación de restricciones de transacciones.

A diferencia de las claves externas aplicadas, Spanner no valida las restricciones en las claves externas informativas. Esto significa que, si usas una clave externa informativa en esta situación, una transacción que intente insertar o actualizar una fila en la tabla Orders que tenga un CustomerId o ProductId que no se encuentre en las tablas Customers y Products no se valida y la transacción no falla. Además, a diferencia de las claves externas aplicadas, las claves externas informativas solo son compatibles con GoogleSQL y no con PostgreSQL.

Características de las claves externas

A continuación, se muestra una lista de las características de las claves externas en Spanner.

  • La tabla que define la clave externa es la tabla de referencia, y las columnas de clave externa son las columnas de referencia.

  • La clave externa hace referencia a las columnas referenciadas de la tabla referenciada.

  • Como en el ejemplo, puedes asignar un nombre a cada restricción de clave externa. Si no especificas un nombre, Spanner generará uno por ti. Puedes consultar el nombre generado desde la tabla INFORMATION_SCHEMA de Spanner. Los nombres de las restricciones se limitan 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. Por ejemplo, la primera columna de referencia hace referencia a la primera columna a la que se hace referencia y la segunda columna de referencia hace referencia a la segunda columna a la que se hace referencia.

  • Una columna referente y su contraparte referenciada deben ser del mismo tipo. Debes poder indexar las columnas.

  • No puedes crear claves externas 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 autorreferencial). Un ejemplo es una tabla Employee con una columna 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 se hacen referencia entre sí, ya sea de forma directa o indirecta. La tabla a la que se hace referencia debe existir antes de crear una clave externa. Esto significa que se debe agregar al menos una de las claves externas con la sentencia ALTER TABLE.

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

  • Las claves externas no usan los índices secundarios que creaste. En su lugar, crean sus propios índices de respaldo. Los índices de respaldo se pueden usar en las evaluaciones de consultas, incluidas las directivas force_index explícitas. Puedes consultar los nombres de los índices de respaldo desde el esquema de información de Spanner INFORMATION_SCHEMA. Para obtener más información, consulta Índices de respaldo.

Tipos de claves externas

Existen dos tipos de claves externas: aplicadas e informativas. Las claves externas aplicadas de manera forzosa son las predeterminadas y aplican la integridad referencial. Las claves externas informativas no aplican la integridad referencial y se usan mejor para declarar el modelo de datos lógico previsto para la optimización de consultas. Para obtener más detalles, consulta las siguientes secciones de claves externas aplicadas e informativas y la tabla de comparación de clave externa externas.

Claves externas aplicadas

Las claves externas aplicadas, el tipo de clave externa predeterminado en Spanner, aplican la integridad referencial. Debido a que las claves externas aplicadas aplican la integridad referencial, hacen que fallen los intentos de realizar lo siguiente:

  • No se puede agregar una fila a una tabla de referencia que tenga un valor de clave externa que no exista en la tabla de referencia.

  • No se puede borrar una fila de una tabla de referencia a la que hacen referencia las filas de la tabla de referencia.

Se aplican todas las claves externas de PostgreSQL. Las claves externas de GoogleSQL se aplican de forma predeterminada. Debido a que las claves externas se aplican de forma predeterminada, usar la palabra clave ENFORCED para especificar que se aplica una clave externa de GoogleSQL es opcional.

Claves externas informativas

Las claves externas informativas se usan para declarar el modelo de datos lógico previsto para la optimización de consultas. Si bien las claves de tabla referenciadas deben ser únicas para las claves externas informativas, no se aplica la integridad referencial. Si deseas validar de forma selectiva la integridad referencial cuando usas claves externas informativas, debes administrar la lógica de validación en el cliente. Para obtener más información, consulta Usa claves externas informativas.

Usa la palabra clave NOT ENFORCED para especificar que una clave externa de GoogleSQL es informativa. PostgreSQL no admite claves externas informativas.

Comparación de los tipos de clave externa

Tanto las políticas aplicadas como las informativas tienen beneficios. En las siguientes secciones, se comparan los dos tipos de claves externas y se incluyen algunas prácticas recomendadas.

Diferencias de alto nivel entre las clave externa

A grandes rasgos, estas son algunas de las diferencias entre las claves externas aplicadas y las informativas:

  • Aplicación de Medidas por Incumplimiento. Las claves externas aplicadas validan y garantizan la integridad referencial en las operaciones de escritura. Las claves externas informativas no validan ni garantizan la integridad referencial.

  • Almacenamiento. Las claves externas aplicadas pueden requerir almacenamiento adicional para el índice de respaldo en la tabla restringida.

  • Capacidad de procesamiento de escritura. Las claves externas aplicadas pueden generar más sobrecarga en la ruta de escritura que las claves externas informativas.

  • Optimización de consultas. Ambos tipos de claves externas se pueden usar para la optimización de consultas. Cuando se permite que el optimizador use claves externas informativas, es posible que los resultados de la consulta no reflejen los datos reales si estos no coinciden con las relaciones de clave externa informativas (por ejemplo, si algunas claves restringidas no tienen claves de referencia coincidentes en la tabla de referencia).

Tabla de diferencias de claves externas

En la siguiente tabla, se enumeran las diferencias detalladas entre las claves externas aplicadas y las informativas:

Claves externas aplicadas Claves externas informativas
Palabras clave ENFORCED NOT ENFORCED
Compatible con GoogleSQL Sí. Las claves externas en GoogleSQL se aplican de forma predeterminada. Sí.
Compatible con PostgreSQL Sí. Las claves externas en PostgreSQL solo se pueden aplicar. No.
Almacenamiento Las claves externas aplicadas requieren almacenamiento para hasta dos índices de respaldo. Las claves externas informativas requieren almacenamiento para un máximo de un índice de respaldo.
Crea índices de respaldo en las columnas de la tabla referenciada cuando es necesario. Sí. Sí.
Crea índices de respaldo en las columnas de la tabla de referencia cuando sea necesario. Sí. No.
Compatibilidad con acciones de clave externa Sí. No.
Valida y aplica la integridad referencial Sí. No. No tener validación mejora el rendimiento de escritura, pero puede afectar los resultados de las consultas cuando se usan claves externas informativas para la optimización de consultas. Puedes usar la validación del cliente o una clave externa aplicada para garantizar la integridad referencial.

Elige el tipo de clave externa que quieres usar

Puedes usar los siguientes lineamientos para decidir qué tipo de clave externa usar:

Te recomendamos que comiences con claves externas aplicadas. Las claves externas aplicadas mantienen los datos y el modelo lógico coherentes en todo momento. Las claves externas aplicadas son la opción recomendada, a menos que no funcionen para tu caso de uso.

Te recomendamos que consideres las claves externas informativas si se cumple cada una de las siguientes condiciones:

  • Deseas usar el modelo de datos lógico que describe la clave externa informativa en la optimización de consultas.

  • Mantener una integridad referencial estricta no es práctico o afecta el rendimiento de forma significativa. A continuación, se muestran ejemplos de cuándo puedes considerar usar una clave externa informativa:

    • Tu fuente de datos upstream sigue un modelo de coherencia eventual. En este caso, es posible que las actualizaciones realizadas en el sistema de origen no se reflejen de inmediato en Spanner. Debido a que las actualizaciones pueden no ser inmediatas, pueden producirse breves incoherencias en las relaciones de clave externa.

    • Tus datos contienen filas de referencia que tienen una gran cantidad de relaciones de referencia. Las actualizaciones de estas filas pueden usar muchos recursos porque Spanner debe validar o, en algunos casos, borrar todas las filas relacionadas con el mantenimiento de la integridad referencial. En esta situación, las actualizaciones podrían afectar el rendimiento de Spanner y ralentizar las transacciones simultáneas.

  • Tu aplicación puede controlar las posibles inconsistencias de datos y su impacto en los resultados de las consultas.

Usa claves externas informativas

Los siguientes temas son solo para claves externas informativas. Para conocer los temas que se aplican a las claves externas informativas y aplicadas, consulta lo siguiente:

Crea una tabla nueva con una clave externa informativa

Puedes crear y quitar claves externas informativas de tu base de datos de Spanner con sentencias de DDL. Agrega claves externas a una tabla nueva con la sentencia CREATE TABLE. Del mismo modo, puedes agregar o quitar claves externas de una tabla existente con la sentencia ALTER TABLE.

En el siguiente ejemplo, se crea una tabla nueva con una clave externa informativa con GoogleSQL. PostgreSQL no admite las claves externas informativas.

GoogleSQL 

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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
 ) PRIMARY KEY (OrderId);

PostgreSQL 

Not Supported

Para obtener más ejemplos de cómo crear y administrar claves externas, consulta Crea y administra clave externa externas. Para obtener más información sobre las declaraciones de DDL, consulta la referencia de DDL.

Usa claves externas informativas para optimizar las consultas

El optimizador de consultas puede usar tanto las claves externas aplicadas como las claves externas informativas para mejorar el rendimiento de las consultas. El uso de claves externas informativas te permite aprovechar los planes de consulta optimizados sin la sobrecarga de la aplicación estricta de la integridad referencial.

Si habilitas el optimizador de consultas para que use la información de las claves externas informativas, es importante que comprendas que la exactitud de la optimización depende de que los datos sean coherentes con el modelo lógico que describen las claves externas informativas. Si existen incoherencias, es posible que los resultados de la consulta no reflejen los datos reales. Un ejemplo de incoherencia es cuando un valor en una columna restringida no tiene un valor coincidente en una columna de referencia.

De forma predeterminada, el optimizador de consultas usa claves externas NOT ENFORCED. Para cambiar esto, establece la opción de base de datos use_unenforced_foreign_key_for_query_optimization en false. A continuación, se muestra un ejemplo de GoogleSQL que lo demuestra (las claves externas informativas no están disponibles en PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

La sugerencia de la instrucción de consulta booleana @{use_unenforced_foreign_key} anula la opción de la base de datos por consulta que controla si el optimizador usa claves externas NOT ENFORCED. Inhabilitar esta sugerencia o la opción de la base de datos puede ser útil para solucionar problemas de resultados de consultas inesperados. En el siguiente ejemplo, se muestra cómo usar @{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Usa claves externas aplicadas

Los siguientes temas solo se aplican a las claves externas aplicadas. Para conocer los temas que se aplican a las claves externas informativas y aplicadas, consulta lo siguiente:

Crea una tabla nueva con una clave externa aplicada

Puedes crear y quitar claves externas aplicadas de tu base de datos de Spanner con DDL. Agrega claves externas a una tabla nueva con la sentencia CREATE TABLE. Del mismo modo, puedes agregar o quitar una clave externa de una tabla existente con la sentencia ALTER TABLE.

Puedes crear y quitar claves externas de tu base de datos de Spanner con DDL. Agrega claves externas a una tabla nueva con la sentencia CREATE TABLE. Del mismo modo, puedes agregar o quitar una clave externa de una tabla existente con la sentencia ALTER TABLE.

En el siguiente ejemplo, se crea una tabla nueva con una clave externa aplicada.

GoogleSQL 

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL 

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

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 de cómo crear y administrar claves externas, consulta Crea y administra clave externa externas.

Acciones de clave externa

Las acciones de clave externa solo se pueden definir en claves externas aplicadas.

Las acciones de clave externa controlan lo que sucede con la columna restringida cuando se borra o actualiza la columna a la que hace referencia. Spanner admite el uso de las acciones ON DELETE CASCADE y ON DELETE SET NULL.

Con la acción ON DELETE CASCADE, cuando borras una fila a la que hace referencia una clave externa, también se borran todas las filas que hacen referencia a esa clave en la misma transacción.

Con la acción ON DELETE SET NULL, cuando borras una fila a la que hace referencia una clave externa, las columnas de referencia en todas las filas que hacen referencia a esa clave se establecen en NULL en la misma transacción.

Puedes agregar una clave externa con una acción cuando creas tu base de datos con DDL. Usa la sentencia CREATE TABLE para agregar claves externas con una acción a una tabla nueva. Del mismo modo, puedes usar la sentencia ALTER TABLE para agregar una acción de clave externa a una tabla existente o para quitar una acción de clave externa. A continuación, se muestra un ejemplo de cómo crear una tabla nueva con una acción de clave externa.

GoogleSQL 

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

A continuación, se muestra una lista de las características de las acciones de clave externa en Spanner.

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

  • No puedes usar ON DELETE SET NULL en columnas de referencia que formen parte de la clave primaria de la tabla de referencia, marcadas como NOT NULL o columnas generadas.

  • Puedes consultar INFORMATION_SCHEMA para encontrar restricciones de clave externa que tengan una acción.

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

Validación de restricciones

La validación de restricciones solo se aplica a las claves externas aplicadas.

Spanner valida las restricciones de clave externa aplicadas a medida que se confirma una transacción o que los efectos de las operaciones de escritura se hacen visibles para las operaciones posteriores de la transacción.

Un valor insertado en la columna de referencia se compara con los valores de la tabla y las columnas de referencia. No se verifican las filas con valores de referencia NULL, lo que significa que puedes agregarlas a la tabla de referencia.

Spanner valida todas las restricciones referenciales de clave externa aplicables cuando se intenta actualizar datos con sentencias de DML o una API. Todos los cambios pendientes se revierten si alguna restricción no es válida.

La validación se produce inmediatamente después de cada declaración DML. Por ejemplo, debes insertar la fila a la que se hace referencia antes de insertar las filas que hacen referencia a ella. 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 la clave externa aplicada se aplaza hasta que se confirma la transacción. En este caso, se permite insertar primero las filas de referencia.

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

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

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

Acción de borrado de larga duración que establece un valor nulo

Cuando borras una fila de una tabla de referencia, Spanner actualiza todas las filas de las tablas de referencia que tienen una acción ON DELETE SET NULL estableciendo las columnas de referencia en NULL. Esto puede provocar un efecto en cascada, en el que una sola operación de eliminación genera miles de operaciones de actualización. Agregar una restricción de clave externa ON DELETE SET NULL a una tabla o crear una tabla con una puede ralentizar las operaciones de eliminación.

Se superó el límite de mutaciones para la eliminación en cascada de la clave externa

Borrar una gran cantidad de registros con una eliminación en cascada de clave externa puede afectar el rendimiento. Esto se debe a que cada registro borrado invoca la eliminación de todos los registros relacionados con él. Si necesitas borrar una gran cantidad de registros con una eliminación en cascada de clave externa, borra de forma explícita las filas de las tablas secundarias antes de borrar la fila de las tablas superiores. Esto evita que la transacción falle debido al límite de mutaciones.

Se superó el límite de mutaciones para la clave externa delete set null

Borrar filas que activan una gran cantidad de actualizaciones con una acción ON DELETE SET NULL de clave externa puede afectar el rendimiento. Esto se debe a que cada fila borrada en la tabla de referencia activa la actualización de todas las filas que hacen referencia a ella en la tabla de referencia. Si necesitas borrar una fila que activa muchas actualizaciones con ON DELETE SET NULL, actualiza de forma explícita las filas de referencia a NULL o bórralas antes de borrar la fila de referencia. Esta práctica evita que la transacción falle debido al límite de mutaciones.

Uso de recursos para las acciones de eliminación de clave externa

ON DELETE CASCADE y ON DELETE SET NULL tienen diferentes costos de recursos y mutaciones según tu esquema:

  • Mutaciones de la tabla base: ON DELETE SET NULL actualiza columnas individuales, por lo que cada fila de referencia actualizada cuenta como varias mutaciones (para la clave de fila y cada columna establecida en NULL). ON DELETE CASCADE cuenta como una mutación por fila borrada o una mutación por rango de claves borrado cuando las columnas de referencia son un prefijo de la clave primaria de la tabla de referencia.
  • Mutaciones de índice: Ambas acciones usan una mutación de eliminación por fila en el índice de respaldo de la clave externa. Para otros índices secundarios en la tabla de referencia, ON DELETE SET NULL solo modifica los índices que incluyen las columnas de referencia, mientras que ON DELETE CASCADE borra las entradas de cada índice secundario en la tabla.
  • Lecturas: ON DELETE SET NULL siempre lee el índice de respaldo de la clave externa para encontrar las filas que coinciden. ON DELETE CASCADE puede omitir esta lectura y borrar un rango de claves directamente cuando las columnas de referencia son un prefijo de la clave primaria de la tabla de referencia.
  • En cascada: ON DELETE SET NULL nunca se aplica en cascada a las tablas secundarias intercaladas, ya que las columnas de clave primaria no se pueden establecer en NULL. Solo activa acciones adicionales si otra clave externa hace referencia a la columna actualizada. ON DELETE CASCADE borra toda la fila de referencia, por lo que también se aplica en cascada a cualquier tabla secundaria intercalada con ON DELETE CASCADE y a las claves externas posteriores.

Comparación de las claves externas aplicadas y la intercalación de tablas

La intercalación de tablas de Spanner es una buena opción para muchas relaciones entre tablas superiores y secundarias en las que la clave primaria de la tabla secundaria incluye las columnas de clave primaria de la tabla superior. 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 elementos principales y secundarios 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 externa, tanto como tabla superior en algunas relaciones como tabla secundaria en otras. Sin embargo, una relación de clave externa no implica la ubicación conjunta de las tablas en la capa de almacenamiento.

Considera un ejemplo en el que se usa una tabla Orders que se define de la siguiente manera:

Esquema de la base de datos con claves externas

Figura 3. Diagrama del esquema de la base de datos con claves externas aplicadas

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

Imagina que tus clientes quieren poder pedir más de un producto por pedido. Puedes mejorar tu diseño si introduces una tabla OrderItems que contenga una entrada para cada producto que pidió el cliente. Puedes introducir otra clave externa aplicada para representar esta nueva relación de uno a varios entre Orders y OrderItems. Sin embargo, también sabes que, a menudo, querrás ejecutar consultas en los pedidos y sus respectivos artículos. Debido a que la ubicación conjunta de estos datos aumenta el rendimiento, te recomendamos crear la relación superior-secundario con la capacidad de intercalación de tablas de Spanner.

A continuación, se muestra cómo definir la tabla OrderItems, intercalada con Orders.

GoogleSQL 

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

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 Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

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.

Esquema de base de datos que muestra una relación de uno a varios entre Orders y la nueva tabla intercalada OrderItems

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

En esta configuración, puedes tener varias entradas OrderItems en cada pedido, y las entradas OrderItems de cada pedido se intercalan y, por lo tanto, se ubican junto con los pedidos. Intercalar físicamente Orders y OrderItems de esta manera puede mejorar el rendimiento, ya que une previamente las tablas de forma eficaz y te permite acceder a las filas relacionadas juntas, a la vez que minimiza los accesos al disco. Por ejemplo, Spanner puede realizar uniones por clave primaria de forma 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, la transacción falla. Estas eliminaciones en cascada grandes funcionan bien para las tablas con una relación "intercalada en el elemento 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 gran cantidad de filas, primero debes borrar de forma explícita las filas de las tablas secundarias.

Si tienes una tabla de usuarios con una relación de clave externa con 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 la tabla superior".

Tabla de comparación

En la siguiente tabla, se resume cómo se comparan las claves externas aplicadas 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 aplicadas
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 No. La intercalación de tablas no se puede quitar después de crearla, a menos que borres toda la tabla secundaria. Sí

Índices de respaldo

Las claves externas no usan índices creados por el usuario. En cambio, crean sus propios índices de respaldo. Las claves externas aplicadas y informativas crean índices de respaldo de manera diferente en Spanner:

  • En el caso de las claves externas aplicadas, 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.

  • En el caso de las claves externas informativas, Spanner puede crear hasta un índice de respaldo cuando sea necesario para las columnas a las que se hace referencia. Las claves externas informativas no crean un índice de respaldo para las columnas de referencia.

En el caso de las claves externas aplicadas y de información, una clave externa suele hacer referencia a las claves primarias de la tabla a la que se hace referencia, por lo que, por lo general, no se necesita un índice para la tabla a la que se hace referencia. Por este motivo, las claves externas informativas suelen tener cero índices de respaldo. Cuando es necesario, el índice de respaldo creado para la tabla referenciada es un índice UNIQUE NULL_FILTERED. La creación de la clave externa falla si algún dato existente infringe la restricción de unicidad del índice.

Las claves externas informativas no tienen un índice de respaldo para la tabla de referencia. En el caso de las claves externas aplicadas, el índice de respaldo de la tabla de referencia es NULL_FILTERED.

Si dos o más claves externas requieren el mismo índice de respaldo, Spanner crea un solo índice para cada una de ellas. Los índices de respaldo se descartan cuando se descartan las claves externas que los usan. No puedes 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 respaldo. Las filas dentro de INFORMATION_SCHEMA.INDEXES que tienen un valor SPANNER_IS_MANAGED de true describen índices de respaldo.

Fuera de las consultas en SQL que invocan directamente el esquema de información, la consola deGoogle Cloud no muestra ninguna información sobre los índices de respaldo de una base de datos.

Cambios de esquema de larga duración

Agregar una clave externa aplicada 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 ella hasta que se complete la operación de larga duración.

En la siguiente tabla, se muestra lo que sucede en Spanner cuando una clave externa aplicada y una informativa se encuentran en una tabla nueva o existente:

Tipo de tabla Clave externa aplicada Clave externa informativa
Nuevo Spanner reabastece los índices a los que se hace referencia según sea necesario para cada clave externa. Spanner reabastece los índices a los que se hace referencia según sea necesario para cada clave externa.
Existente Spanner reabastece los índices de referencia y referenciados según sea necesario. Spanner también valida los datos existentes en la tabla para garantizar que cumplan con la restricción de integridad referencial de la clave externa. El cambio de esquema falla si algún dato no es válido. Spanner reabastece el índice al que se hace referencia según sea necesario y no valida los datos existentes en la tabla.

Las siguientes opciones no son compatibles:

  • Agregar una acción de clave externa a una restricción de clave externa aplicada existente.
  • Cambiar la aplicación de una clave externa existente.

En ambos casos, te recomendamos que hagas lo siguiente:

  1. Agrega una nueva restricción con la acción o la aplicación de medidas por incumplimiento requeridas.
  2. Descarta la restricción anterior.

Agregar una restricción nueva y descartar la anterior evita un problema de operación de alteración de restricción de larga duración. Por ejemplo, supongamos que deseas agregar una acción DELETE CASCADE en una clave externa existente. Después de crear la nueva clave externa con la acción ON DELETE CASCADE, el efecto de ambas restricciones es una acción DELETE CASCADE. Luego, puedes descartar la restricción anterior de forma segura.

La eliminación de una restricción puede provocar la eliminación de los índices de respaldo de la clave externa si las otras restricciones de clave externa no usan los índices. Por este motivo, si primero descartas la restricción anterior, agregar la misma restricción de clave externa con una acción más adelante podría generar operaciones de larga duración, como reabastecer índices, validar restricciones de índice únicas o validar restricciones referenciales de clave externa.

Puedes consultar INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE para verificar el estado de creación de la clave externa.

¿Qué sigue?