Crear y gestionar relaciones de claves externas

En esta página se describe cómo gestionar las relaciones de claves externas en tu 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 se asegura de 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.

Esquema de base de datos de ejemplo que muestra las relaciones de clave externa entre tablas.

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

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

  • La tabla Customers registra los nombres de cada cliente.
  • En la tabla Orders se registran todos los pedidos realizados.
  • La tabla Products almacena la información de cada producto.

Hay dos relaciones de clave externa entre estas tablas:

  • Se define una relación de clave externa entre la tabla Orders y la tabla Customers para asegurarse de 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 asegura que no se pueda crear un pedido de un producto que no exista.

Tomando como ejemplo el esquema anterior, en este tema se describen las instrucciones del lenguaje de definición de datos (DDL) CONSTRAINT que puedes usar para gestionar las relaciones entre las tablas de una base de datos.

De forma predeterminada, todas las claves externas de Spanner son claves externas obligatorias, que aplican la integridad referencial. En Spanner, también puedes 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 Elegir el tipo de clave externa que se va a usar. Si no se especifica, las claves externas de los ejemplos de esta página son claves externas obligatorias.

Añadir una clave externa a una tabla

Supongamos que ha creado una tabla Customers en su base de datos de pedidos de productos básicos. Ahora necesitas una tabla Orders para almacenar información sobre los pedidos que hacen los clientes. Para asegurarse de que todos los pedidos sean válidos, no debe permitir 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 obligatoria para establecer una relación entre las dos tablas. Una opción es añadir una columna CustomerID a la nueva tabla y usarla como clave externa para crear una relación con la columna CustomerID de la tabla Customers.

Cuando creas una tabla con una clave externa, usas REFERENCE para establecer una relación con otra tabla. La tabla que contiene la instrucción REFERENCE se denomina tabla de referencia. La tabla cuyo nombre se indica en la instrucción REFERENCE es la tabla de referencia. La columna cuyo nombre se indica en la instrucción REFERENCE se denomina columna de referencia.

En el siguiente ejemplo se muestra cómo usar la instrucción 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 instrucción anterior contiene una cláusula CONSTRAINT que tiene las siguientes características:

  • Uso de la sintaxis CONSTRAINT para asignar un nombre a una restricción, lo que facilita la eliminación de la tabla con el nombre que hayas elegido.

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

  • La tabla Orders en la que se define la restricción es la tabla de referencia. La tabla Customers es la tabla a la que se hace referencia.

  • La columna de referencia de 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.

En el siguiente ejemplo se muestra una instrucción alternativa para crear una tabla. En este caso, la restricción de clave externa se define sin un nombre. Si usas esta sintaxis, Spanner generará un nombre automáticamente. Para ver los nombres de todas las claves externas, consulta Ver propiedades de una relación de clave 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)
);

Añadir una clave externa a una tabla disponible

También debes asegurarte de que los clientes solo puedan pedir productos que estén disponibles. Si la tabla tiene restricciones, debes eliminar todas las restricciones. En Spanner, todas las restricciones obligatorias de una tabla se deben implementar al mismo tiempo en una sola instrucción DDL por lotes.

Si tu tabla no tiene ninguna restricción, puedes usar la instrucción DDL ALTER TABLE para añadir una restricción de clave externa obligatoria a la tabla Orders, como se muestra en el siguiente ejemplo:

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

La columna de referencia de Orders es ProductID y hace referencia a la columna ProductID de Products. Si no te importa que Spanner asigne nombres a estas restricciones, usa la siguiente sintaxis:

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

Añadir 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 añadir una restricción de clave externa que haga referencia a la tabla Customers. Sin embargo, quieres asegurarte de que, cuando elimines un registro de cliente en el futuro, Spanner también elimine 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 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 instrucción 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 de la tabla Orders también debe estar en la tabla Customers. Si alguien intenta eliminar una fila de la tabla Customers, todas las filas de la tabla Orders que hagan referencia al valor CustomerID eliminado también se eliminarán en la misma transacción.

Añadir una clave externa con una acción de eliminación a una tabla

También debes asegurarte de que los pedidos solo se creen para productos que existan. Puedes usar ALTER TABLE para añadir 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 se elimina una fila de la tabla Products, se eliminarán todas las filas de la tabla Orders que hagan referencia al valor ProductID eliminado.

Usar claves externas informativas (solo GoogleSQL)

Las claves externas informativas permiten que el optimizador de consultas use la relación de clave externa sin la sobrecarga que suponen las comprobaciones de integridad referencial que realizan las claves externas obligatorias. Las claves externas informativas son útiles cuando no es práctico aplicar una integridad referencial estricta o cuando esto supone una sobrecarga de rendimiento significativa.

Siguiendo con el ejemplo anterior, supongamos que quiere modelizar las relaciones entre las tablas Customers, Orders y Products. Sin embargo, si se aplica una integridad referencial estricta en los datos de las tablas, pueden producirse cuellos de botella en el rendimiento, sobre todo durante los periodos de mayor actividad de compra, en los que el volumen de pedidos es alto. Además, los clientes pueden hacer pedidos de productos que se hayan retirado y eliminado 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);

Al crear una clave externa informativa con NOT ENFORCED, permites que un pedido pueda hacer referencia a un cliente o producto que no exista. Usar una clave externa informativa en lugar de una restricción de clave externa obligatoria es una buena opción si es posible que se elimine una cuenta de cliente o que se deje de ofrecer un producto. Con una clave externa informativa, Spanner no realiza validaciones de integridad referencial. De esta forma, se reduce la sobrecarga de escritura, lo que puede mejorar el rendimiento durante las horas punta de procesamiento de pedidos.

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

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

Integridad referencial con claves externas obligatorias

El motivo principal para añadir relaciones de claves externas obligatorias es que Spanner pueda mantener la integridad referencial de tus datos. Si modificas los datos de forma que se incumpla una restricción de clave externa, la actualización fallará y se producirá un error.

Toma como referencia los datos de la figura 2. Algunos clientes han pedido productos, como se muestra en la tabla Orders. Debido a la restricción de clave externa obligatoria que se ha implementado, los datos que se han insertado en la tabla Orders tienen integridad referencial.

Datos de ejemplo de las tablas `Customers`, `Products` y `Orders`.

Imagen 2. Datos de muestra de nuestra base de datos de pedidos.

En los siguientes ejemplos se muestra qué ocurre cuando intentas modificar los datos de forma que se rompa la integridad referencial.

  • Añada una fila a la tabla Orders con un valor CustomerID que no exista en Customers.

    ¿Qué ocurre si intentas hacer la siguiente modificación con los datos de ejemplo 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 lo hiciera, tendrías un pedido no válido en tu sistema. Sin embargo, con la restricción de clave externa obligatoria que has añadido a tu tabla Orders, tu tabla está protegida. La INSERT falla y muestra 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 obligatorias, las claves externas informativas no aplican la integridad referencial. Si FK_CustomerOrder es una clave externa informativa, la instrucción de inserción 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 eliminar una fila de la tabla Customers cuando se hace referencia al cliente en una restricción de clave externa obligatoria.

    Imagina una situación en la que un cliente se da de baja de tu tienda online. Quieres eliminar 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 aún hay registros en la tabla Orders que hacen referencia a la fila de cliente que estás intentando eliminar. 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 debe eliminar todas las entradas de referencia en Orders. También puedes definir la clave externa con la acción ON DELETE CASCADE para que Spanner gestione la eliminación de las entradas de referencia.

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

Ver propiedades de una relación de clave externa

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 a las que puedes responder consultando INFORMATION SCHEMA.

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

¿Qué restricciones se definen en mi 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 han definido en mi base de datos?

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

¿Qué índices son índices secundarios de claves externas, también conocidos como índices de reserva?

Spanner gestiona los índices de reserva de las claves externas, por lo que, al consultar SPANNER_IS_MANAGED en la vista INDEXES, se devuelven todos los índices de reserva.

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

¿Qué 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?

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 Information Schema.

Eliminar una relación de clave externa

La siguiente DDL elimina 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 eliminan automáticamente cuando se elimina la propia restricción.

Compatibilidad con relaciones de claves externas más complejas

En los siguientes temas se explica 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 principal de una tabla o a un índice de respaldo. La tabla de referencia contiene claves externas de la clave de la tabla de referencia.

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

  • Cada valor de SongName de 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 correspondiente 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, quizás por motivos antiguos o debido a la desnormalización. Las claves externas de Spanner permiten referencias circulares. Como una tabla de referencia debe existir antes de que una clave externa pueda hacer referencia a ella, una de las claves externas debe añadirse con una instrucción ALTER TABLE. Aquí tienes un ejemplo

  1. Crea TableA sin 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 de autorreferencia

Un tipo especial de referencia circular es una tabla que define una clave externa que hace referencia a la misma tabla. Por ejemplo, el siguiente fragmento muestra una clave externa para asegurarse de que el valor de 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)
);

Siguientes pasos