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 un vínculo entre los datos relacionados. Cuando usas una clave externa, Spanner garantiza que se mantenga esa relación.
En el siguiente diagrama, se muestra un esquema de base de datos simple donde 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
En el esquema que se muestra en la Figura 1, hay tres tablas:
- 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 tablaCustomers
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 laProducts
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 los datos
Declaraciones CONSTRAINT
del lenguaje de definición (DDL) que puedes usar para administrar
relaciones entre tablas en una base de datos.
Agregar una clave externa durante la creación de una tabla nueva
Supongamos que creaste una tabla Customers
en tu producto simple.
en la base de datos de pedidos. Ahora necesitas una tabla Orders
para almacenar información sobre el
pedidos que hacen los clientes. Para asegurarte de que todos los pedidos sean válidos,
permitir que el sistema inserte filas en la tabla Orders
, a menos que también haya
entrada coincidente de la tabla Customers
. Por lo tanto, necesitas una clave externa para
establecer una relación entre las dos tablas. Una opción es agregar
CustomerID
a la tabla nueva y usarla como clave externa para crear un
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. El
La tabla nombrada en la sentencia REFERENCE
es la tabla a la que se hace referencia. La columna
nombrada en la sentencia REFERENCE
se llama columna de referencia.
En el siguiente ejemplo, se muestra cómo usar la declaración DDL CREATE TABLE
para
crea la tabla Orders
con una restricción de clave externa que haga referencia
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 sentencia anterior contiene una cláusula CONSTRAINT
que tiene lo siguiente:
características:
Uso de la sintaxis
CONSTRAINT
para nombrar una restricción más fácil eliminar la tabla con el nombre que has elegido.La restricción tiene el nombre
FK_CustomerOrder
. El alcance de los nombres de las restricciones es del esquema y debe ser único dentro de este.La tabla
Orders
, en la que defines la restricción, es la tabla de referencia. La tablaCustomers
es la tabla a la que se hace referencia.La columna de referencia en la tabla de referencia es
CustomerID
. Integra hace referencia al campoCustomerID
de la tablaCustomers
. Si alguien intenta Para insertar una fila enOrders
con unCustomerID
que no existe enCustomers
, la inserción falla.
En el siguiente ejemplo, se muestra una declaración 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 Visualización de las 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)
);
Agregar una clave externa a una tabla existente
También quieres asegurarte de que los clientes solo puedan pedir productos que
existen. Primero, debes descartar la restricción existente. Luego, puedes usar
ALTER TABLE
para agregar otra restricción de clave externa a la tabla Orders
, como
como se muestra a continuación:
ALTER TABLE Orders
ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
La columna referente en Orders
es ProductID
y hace referencia a la columna ProductID
en Products. De nuevo, si estás conforme con que Spanner les asigne un nombre
restricciones, usa la siguiente sintaxis:
ALTER TABLE Orders
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);
Agregar una clave externa con una acción de borrar cuando se cree una tabla nueva
Recuerda el ejemplo anterior en el que tienes una tabla Customers
en un producto.
de pedidos que necesita una tabla Orders
. Quieres agregar una clave externa
restricción que hace referencia a la tabla Customers
. Sin embargo, debes asegurarte
que, cuando borres un registro de cliente en el futuro,
también borra todos los pedidos de ese cliente. En este caso, debes usar
Acción ON DELETE CASCADE
con la restricción de clave externa.
En la siguiente declaración DDL CREATE TABLE
para la tabla Orders
, se incluye lo siguiente:
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
ON DELETE CASCADE
. La columna CustomerID
es una clave externa que
hace referencia al campo CustomerID
de la tabla Customers
. Esto significa que cada
El valor CustomerID
de la tabla Orders
también debe existir en Customers
desde una tabla de particiones. Si alguien intenta borrar una fila de la tabla Customers
, se
se muestran todas las filas de la tabla Orders
que hacen referencia al valor CustomerID
borrado.
también se borran en la misma transacción.
Agregar una clave externa con una acción de eliminación a una tabla existente
También quiere asegurarse de que los pedidos se creen solo para productos que
existen. Puedes usar ALTER TABLE
para agregar otra restricción de clave externa con
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 eliminas una fila de la tabla Products
, se borran todas las filas de la
Tabla Orders
que hacen referencia al valor ProductID
borrado.
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;
Ejemplos de cómo Spanner mantiene la integridad referencial
El motivo principal para agregar relaciones de clave externa es para que Spanner pueda mantener integridad referencial de tus datos. Si modificas los datos de una manera que rompe 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 de pedidos. Debido a las claves externas que están implementadas,
puedes garantizar que los datos que se insertaron en la tabla Orders
tengan
la integridad referencial.
Figura 2. Datos de muestra para la base de datos de pedidos.
En los siguientes ejemplos se muestra lo que sucede cuando se intentan modificar los datos de de una manera que rompa la integridad referencial.
Agrega una fila a la tabla
Orders
con un valorCustomerID
que no tenga existen enCustomers
¿Qué sucede si pruebas la siguiente modificación, dados los datos de muestra del en el 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 unaCustomerID
(447) que no existe en la tablaCustomers
. Si el sistema esto, tenía un pedido no válido en su sistema. Sin embargo, con la restricción de clave externa que agregaste a tu tablaOrders
, tu tabla es están protegidos.INSERT
falla con el siguiente mensaje, si suponemos que restricción se llamaFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Intentar borrar una fila de la tabla
Customers
cuando el cliente esté a la que se hace referencia en una restricción de clave externa.Imagina una situación en la que un cliente anula la suscripción a tu tienda en línea. Quieres quitar al cliente de tu backend, por lo que intentas hacer lo siguiente: una sola 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 haya registros en la tabla
Orders
que hagan referencia al cliente la fila 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
. Tú también puedes definir la clave externa con la acciónON DELETE CASCADE
para permitir Spanner controla la eliminación de las entradas de referencia.
Consultar las propiedades de las relaciones de claves externas
INFORMATION_SCHEMA de Spanner contiene información sobre claves externas y sus índices de respaldo. Estos son algunos ejemplos de las preguntas que puedes responder si consultas INFORMATION SCHEMA.
Para obtener más información sobre los índices de respaldo, consulta Índices de respaldo de claves externas.
¿Qué restricciones se definen en la base de datos?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY';
¿Qué claves externas se definen en la base de datos?
SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;
¿Qué índices son secundarios (de respaldo) para las claves externas?
Spanner administra los índices de respaldo de clave externa , por lo que
SPANNER_IS_MANAGED
en la vista INDEXES
muestra todos los índices de copia de seguridad.
SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED = 'YES';
¿Cuál es la acción referencial 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;
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 claves externas se descartan automáticamente cuando se descarta la restricción.
Compatibilidad con relaciones más complejas de claves externas
Varias columnas
Las claves externas pueden hacer referencia a varias columnas. La lista de columnas forma una clave que corresponde a la clave primaria de una tabla o a un índice de respaldo. La tabla 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 le indican a Spanner
que cada valor de SongName
de la tabla TopHits debe tener un valor coincidente en
la tabla Canciones; y cada par de valores SingerFirstName
y SingerLastName
debe tener un par de valores FirstName
y LastName
que coincidan en el campo Singers
desde una tabla de particiones.
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 externa debe existir antes de que una clave externa pueda hacer referencia a ella, una de las claves externas debe agregarse con un estado ALTER TABLE
. Por ejemplo:
- Crear TablaA, sin una clave externa
- Cree una TablaB con una restricción de clave externa en la TablaA.
- Usa
ALTER TABLE
en TablaA para crear una referencia de clave externa a TablaB.
Tablas que hacen referencia a sí mismas
Un tipo especial de referencia circular es una tabla que define una clave externa que hace referencia a la misma tabla. Por ejemplo, en el siguiente fragmento, se muestra una clave externa para garantizar que el ID del administrador 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.