Usar claves primarias y externas

Las claves principales y externas son restricciones de tabla que pueden ayudar a optimizar las consultas. En este documento se explica cómo crear, ver y gestionar restricciones, así como usarlas para optimizar las consultas.

BigQuery admite las siguientes restricciones de clave:

  • Clave principal: es una combinación de una o varias columnas de una tabla que es única para cada fila y no es NULL.
  • Clave externa: es una combinación de una o varias columnas de una tabla que está presente en la columna de clave principal de una tabla a la que se hace referencia o que es NULL.

Las claves principales y externas se suelen usar para asegurar la integridad de los datos y permitir la optimización de las consultas. BigQuery no aplica restricciones de clave principal ni de clave externa. Cuando declares restricciones en tus tablas, debes asegurarte de que tus datos las cumplan. BigQuery puede usar restricciones de tabla para optimizar las consultas.

Gestionar restricciones

Las relaciones de clave principal y clave externa se pueden crear y gestionar mediante las siguientes instrucciones DDL:

También puedes gestionar las restricciones de tabla a través de la API de BigQuery actualizando el objeto TableConstraints.

Ver restricciones

Las siguientes vistas proporcionan información sobre las restricciones de las tablas:

Optimizar consultas

Si crea y aplica claves principales y externas en sus tablas, BigQuery puede usar esa información para eliminar u optimizar determinadas combinaciones de consultas. Aunque es posible imitar estas optimizaciones reescribiendo las consultas, no siempre es práctico.

En un entorno de producción, puede crear vistas que combinen muchas tablas de hechos y de dimensiones. Los desarrolladores pueden consultar las vistas en lugar de consultar las tablas subyacentes y reescribir manualmente las combinaciones cada vez. Si define las restricciones adecuadas, las optimizaciones de las combinaciones se aplican automáticamente a las consultas a las que se apliquen.

Los ejemplos de las siguientes secciones hacen referencia a las tablas store_sales y customer con restricciones:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

Eliminar las combinaciones internas

Considera la siguiente consulta que contiene un INNER JOIN:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

La columna customer_name es una clave principal de la tabla customer, por lo que cada fila de la tabla store_sales tiene una sola coincidencia o ninguna si sales_customer es NULL. Como la consulta solo selecciona columnas de la tabla store_sales, el optimizador de consultas puede eliminar la combinación y reescribir la consulta de la siguiente manera:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

Eliminar combinaciones externas

Para quitar una LEFT OUTER JOIN, las claves de unión de la derecha deben ser únicas y solo se deben seleccionar las columnas de la izquierda. Observa la consulta siguiente:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

En este ejemplo, no hay ninguna relación entre category y customer_name. Las columnas seleccionadas solo proceden de la tabla store_sales y la clave de unión customer_name es una clave principal de la tabla customer, por lo que cada valor es único. Esto significa que hay exactamente una (posiblemente NULL) coincidencia en la tabla customer por cada fila de la tabla store_sales y que se puede eliminar la LEFT OUTER JOIN:

SELECT ss.*
FROM mydataset.store_sales;

Reordenar uniones

Cuando BigQuery no puede eliminar una combinación, puede usar restricciones de tabla para obtener información sobre las cardinalidades de las combinaciones y optimizar el orden en el que se realizan las combinaciones.

Limitaciones

Las claves primarias y externas están sujetas a las siguientes limitaciones:

  • Las restricciones de clave no se aplican en BigQuery. Eres responsable de mantener las restricciones en todo momento. Las consultas en tablas con restricciones infringidas pueden devolver resultados incorrectos.
  • Las claves principales no pueden superar las 16 columnas.
  • Las claves externas deben tener valores que estén presentes en la columna de la tabla de referencia. Estos valores pueden ser NULL.
  • Las claves principales y externas deben ser de uno de los siguientes tipos: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING o TIMESTAMP.
  • Las claves primarias y externas solo se pueden definir en columnas de nivel superior.
  • Las claves principales no pueden tener nombre.
  • No se pueden cambiar los nombres de las tablas con restricciones de clave principal.
  • Una tabla puede tener hasta 64 claves externas.
  • Una clave externa no puede hacer referencia a una columna de la misma tabla.
  • No se puede cambiar el nombre ni el tipo de los campos que forman parte de las restricciones de clave principal o de clave externa.
  • Si copias, clonas, restauras o creas una instantánea de una tabla sin la opción -a o --append_table, las restricciones de la tabla de origen se copiarán y sobrescribirán en la tabla de destino. Si usa la opción -a o --append_table, solo se añadirán los registros de la tabla de origen a la tabla de destino sin las restricciones de la tabla.

Siguientes pasos