Crear y administrar relaciones de claves externas

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

Una clave externa es una columna que se comparte entre tablas para establecer un vínculo entre datos relacionados. Cuando usas una clave externa, Spanner garantiza que se mantenga esta relación.

En el siguiente diagrama, se muestra un esquema de base de datos básico en el que los datos de una tabla tienen una relación con los datos de otra tabla.

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 tabla Products garantiza que no se pueda crear un pedido para un producto que no existe.

Con el esquema anterior como ejemplo, en este tema se analizan las declaraciones de lenguaje de definición de datos (DDL) CONSTRAINT que puedes usar para administrar las relaciones entre las tablas de una base de datos.

De forma predeterminada, todas las claves externas en Spanner son claves externas aplicadas, que aplican la integridad referencial. En Spanner, también puedes elegir usar claves externas informativas, que no validan ni aplican la integridad referencial. Para obtener más información, consulta Comparación de claves externas y Elige qué tipo de clave externa usar. Cuando no se especifica, las claves externas de los ejemplos de esta página son claves externas aplicadas.

Agrega una clave externa a una tabla nueva

Supongamos que creaste una tabla Customers en tu base de datos básica de pedidos de productos. Ahora necesitas una tabla Orders para almacenar información sobre los pedidos que realizan los clientes. Para garantizar que todos los pedidos sean válidos, no quieres que el sistema inserte filas en la tabla Orders, a menos que también haya una entrada coincidente en la tabla Customers. Por lo tanto, necesitas una clave externa aplicada para establecer una relación entre las dos tablas. Una opción es agregar una columna CustomerID a la tabla nueva y usarla como clave externa para crear una relación con la columna CustomerID de la tabla Customers.

Cuando creas una tabla nueva con una clave externa, usas REFERENCE para establecer una relación con otra tabla. La tabla que contiene la sentencia REFERENCE se denomina tabla de referencia. La tabla que se nombra en la sentencia REFERENCE es la tabla referenciada. La columna que se nombra en la sentencia REFERENCE se denomina columna de referencia.

En el siguiente ejemplo, se muestra cómo usar la declaración de DDL CREATE TABLE para crear la tabla Orders con una restricción de clave externa que hace referencia a CustomerID en la tabla 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)
);

La declaración anterior contiene una cláusula CONSTRAINT que tiene las siguientes características:

• Uso de la sintaxis CONSTRAINT para nombrar una restricción, lo que facilita la eliminación de la tabla con 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 de él.

• La tabla Orders, en la que defines la restricción, es la tabla de referencia. La tabla Customers es la tabla de referencia.

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

Nota: A menudo, las columnas a las que se hace referencia en la tabla corresponden a las columnas de claves primarias, pero esto no es obligatorio. Una tabla referente también puede hacer referencia a columnas no principales de una tabla a la que se hace referencia.

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

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

Agregar una clave externa a una tabla existente

También quieres asegurarte de que los clientes solo puedan pedir productos que existan. Si tu tabla tiene restricciones existentes, debes borrar todas las restricciones. En Spanner, todas las restricciones aplicadas en una tabla se deben implementar al mismo tiempo en una sola sentencia de DDL por lotes.

Si tu tabla no tiene restricciones existentes, puedes usar la declaración de DDL ALTER TABLE para agregar una restricción de clave externa aplicada a la tabla Orders existente, como se muestra en el siguiente ejemplo:

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

La columna de referencia en Orders es ProductID, y hace referencia a la columna ProductID en Products. Si estás de acuerdo con que Spanner asigne un nombre a estas restricciones, usa la siguiente sintaxis:

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

Agrega una clave externa con una acción de eliminación a una tabla nueva

Recuerda el ejemplo anterior en el que tienes una tabla Customers en una base de datos de pedidos de productos que necesita una tabla Orders. Quieres agregar una restricción de clave externa que haga referencia a la tabla Customers. Sin embargo, quieres asegurarte de que, cuando borres un registro de cliente en el futuro, Spanner también borre todos los pedidos de ese cliente. En este caso, debes usar la acción ON DELETE CASCADE con la restricción de clave externa.

La siguiente declaración de DDL de CREATE TABLE para la tabla Orders incluye la restricción de clave externa que hace referencia a la tabla Customers con una acción 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)
);

La sentencia anterior contiene una restricción de clave externa con una cláusula ON DELETE CASCADE. La columna CustomerID es una clave externa que hace referencia al campo CustomerID de la tabla Customers. Esto significa que cada valor CustomerID en la tabla Orders también debe existir en la tabla Customers. Si alguien intenta borrar una fila de la tabla Customers, todas las filas de la tabla Orders que hacen referencia al valor CustomerID borrado también se borran en la misma transacción.

Del mismo modo, puedes usar la acción ON DELETE SET NULL para establecer las columnas de referencia en NULL en lugar de borrar las filas de referencia. Para usar ON DELETE SET NULL, las columnas de referencia deben admitir valores nulos (es decir, no deben estar marcadas como NOT NULL). No pueden formar parte de la clave primaria ni de las columnas generadas de la tabla de referencia.

Para obtener detalles sobre cómo se comparan ON DELETE SET NULL y ON DELETE CASCADE en las mutaciones, las lecturas y el comportamiento en cascada, consulta Uso de recursos para las acciones de eliminación de clave externa externas.

La siguiente declaración de DDL CREATE TABLE para la tabla Orders incluye una restricción de clave externa que hace referencia a la tabla Customers con una acción ON DELETE SET NULL. En este ejemplo, CustomerID en la tabla Orders admite valores nulos.

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: Si no especificas una acción de clave externa, la acción predeterminada es NO ACTION. Esto significa que, si alguien intenta borrar una fila de la tabla Customers y hay filas en la tabla Orders que hacen referencia al valor CustomerID borrado, la operación de eliminación falla con un error de incumplimiento de restricción de clave externa externa.

Agrega una clave externa con una acción de eliminación a una tabla

También quieres asegurarte de que solo se creen pedidos para productos que existen. Puedes usar ALTER TABLE para agregar otra restricción de clave externa con la acción ON DELETE CASCADE a la tabla de pedidos de la siguiente manera:

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

Si borras una fila de la tabla Products, se borrarán todas las filas de la tabla Orders que hagan referencia al valor ProductID borrado. También puedes usar ON DELETE SET NULL en una sentencia ALTER TABLE si la columna de referencia admite valores nulos, no forma parte de la clave primaria y no es una columna generada.

Nota: No se admite agregar o quitar 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. Para obtener más información, consulta Cambios de esquema de larga duración.

Usa claves externas informativas (solo GoogleSQL)

Las claves externas informativas permiten que el optimizador de consultas aproveche la relación de clave externa sin la sobrecarga que generan las verificaciones de integridad referencial que realizan las claves externas aplicadas. Las claves externas informativas son útiles cuando aplicar una integridad referencial estricta no es práctico o genera una sobrecarga de rendimiento significativa.

Para continuar con el ejemplo anterior, imagina que quieres modelar las relaciones entre las tablas Customers, Orders y Products. Sin embargo, aplicar una integridad referencial estricta en los datos de las tablas podría generar cuellos de botella en el rendimiento, en especial durante los períodos de compras de mayor actividad con grandes volúmenes de pedidos. Además, los clientes pueden realizar pedidos de productos que se descontinuaron y quitaron de la tabla Products.

Puedes crear la tabla Orders con claves externas informativas:

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

Si creas una clave externa informativa con NOT ENFORCED, permites la posibilidad de que un pedido haga referencia a un cliente o producto inexistente. Usar una clave externa informativa en lugar de una restricción de clave externa aplicada es una buena opción si se puede borrar una cuenta de cliente o se puede descontinuar un producto. Con una clave externa informativa, Spanner no realiza la validación de integridad referencial. Esto reduce la sobrecarga de escritura, lo que puede mejorar el rendimiento durante los momentos de mayor procesamiento de pedidos.

Puedes permitir que el optimizador de consultas use las relaciones para generar planes de consultas eficientes. Esto puede mejorar el rendimiento de las consultas que unen las tablas en columnas de clave externa. Para obtener más información, consulta clave externa informativa para la optimización de consultas.

Precaución: Si la lógica de tu aplicación requiere una integridad referencial estricta (por ejemplo, evitar pedidos de productos que no existen), debes implementar verificaciones adicionales a nivel de la aplicación. Los resultados de la consulta podrían verse afectados si hay incoherencias en los datos. Por ejemplo, una consulta que une Orders con Products podría devolver un pedido con un ProductID descontinuado si el optimizador usa una clave externa informativa.

Consulta datos en relaciones de claves externas

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

Integridad referencial con claves externas aplicadas

El motivo principal para agregar relaciones de clave externa aplicadas es que Spanner pueda mantener la integridad referencial de tus datos. Si modificas los datos de una manera que incumple una restricción de clave externa, la actualización falla con un error.

Considera los datos de la figura 2. Algunos clientes pidieron productos, como se muestra en la tabla Orders. Debido a la restricción de clave externa aplicada que está vigente, los datos que se insertaron en la tabla Orders tienen integridad referencial.

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

En los siguientes ejemplos, se muestra lo que sucede cuando intentas modificar los datos de una manera que rompería la integridad referencial.

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

  ¿Qué sucede si intentas realizar la siguiente modificación, dados los datos de muestra del diagrama anterior?

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

  En este caso, el sistema intentaría insertar una fila en Orders con un CustomerID (447) que no existe en la tabla Customers. Si el sistema hiciera esto, tendrías un pedido no válido en tu sistema. Sin embargo, con la restricción de clave externa aplicada que agregaste a la tabla Orders, la tabla está protegida. La INSERT falla con el siguiente mensaje, suponiendo que la restricción se llama FK_CustomerOrder.

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

  A diferencia de las claves externas aplicadas, las claves externas informativas no aplican la integridad referencial. Si FK_CustomerOrder es una clave externa informativa, la sentencia INSERT se ejecuta correctamente porque Spanner no valida que el CustomerID correspondiente exista en la tabla Customers. Por este motivo, es posible que los datos no se ajusten a la integridad referencial definida por FK_CustomerOrder.

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

  Imagina una situación en la que un cliente cancela la suscripción a tu tienda en línea. Quieres quitar al cliente de tu backend, por lo que intentas realizar la siguiente operación.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  En este ejemplo, Spanner detecta a través de la restricción de clave externa que todavía hay registros en la tabla Orders que hacen referencia a la fila del cliente que intentas 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 borra todas las entradas de referencia en Orders. También puedes definir la clave externa con la acción ON DELETE CASCADE para permitir que Spanner controle la eliminación de las entradas de referencia.

  Del mismo modo, si FK_CustomerOrder es una clave externa informativa, la acción de borrar se realiza correctamente porque Spanner no garantiza la integridad referencial de las claves externas informativas.

Consultar las propiedades de las relaciones de claves externas

El INFORMATION_SCHEMA de Spanner contiene información sobre las claves externas y sus índices de respaldo. A continuación, se muestran algunos ejemplos de las preguntas que puedes responder consultando 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;

¿Cuáles son los índices secundarios para las claves externas, también conocidos como índices de respaldo?

Spanner administra los índices de respaldo de claves externas , por lo que consultar SPANNER_IS_MANAGED en la vista INDEXES muestra todos los índices de respaldo.

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

¿Cuál es la acción de referencia definida con la restricción de clave externa?

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

¿Se aplica o no se aplica una clave externa?

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

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

Quitar relaciones de claves externas

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 la clave externa se descartan automáticamente cuando se descarta la restricción.

Compatibilidad con relaciones más complejas de claves externas

En los siguientes temas, se muestra cómo usar claves externas para aplicar relaciones más complejas entre tablas.

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 referente contiene claves externas de la clave de la tabla a la que se hace referencia.

En el siguiente ejemplo, las definiciones de clave externa aplicadas indican lo siguiente:

• Cada valor de SongName en la tabla TopHits debe tener un valor coincidente en la tabla Songs.

• Cada par de valores SingerFirstName y SingerLastName debe tener un par de valores FirstName y LastName coincidente en la tabla 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)
);

Referencias circulares

En ocasiones, las tablas tienen dependencias circulares, tal vez por motivos heredados o debido a la desnormalización. Las claves externas de Spanner permiten referencias circulares. Dado que una tabla de referencia debe existir antes de que una clave externa pueda hacer referencia a ella, una de las claves externas se debe agregar con una sentencia ALTER TABLE. Por ejemplo:

1. Crea TableA sin una clave externa.
2. Crea TableB con una restricción de clave externa en TableA.
3. Usa ALTER TABLE en TableA para crear una referencia de clave externa a TableB.

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 aplicar que el ManagerId de un empleado también sea un empleado.

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

¿Qué sigue?

• Obtén más información sobre la compatibilidad con claves externas en Spanner.

• Obtén más información sobre el ESQUEMA DE INFORMACIÓN de Spanner.