Usar claves primarias y externas

Las claves primarias y externas son restricciones de tabla que pueden ayudar a optimizar las consultas. En este documento, se explica cómo crear, ver y administrar restricciones, y cómo usarlas para optimizar tus consultas.

BigQuery admite las siguientes restricciones de clave:

  • Clave primaria: Una clave primaria para una tabla es una combinación de una o más columnas que es única para cada fila y no es NULL.
  • Clave externa: Una clave externa para una tabla es una combinación de una o más columnas que está presente en la columna de clave primaria de una tabla referenciada o es NULL.

Las claves primarias y externas suelen usarse para garantizar la integridad de los datos y permitir la optimización de las consultas. BigQuery no aplica restricciones de claves externas y primarias. Cuando declares restricciones en tus tablas, debes asegurarte de que tus datos las cumplan. BigQuery puede usar restricciones de tabla para optimizar tus consultas.

Administra restricciones

Las relaciones de claves primarias y externas se pueden crear y administrar a través de las siguientes sentencias de DDL:

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

Ver restricciones

Las siguientes vistas te brindan información sobre las restricciones de tu tabla:

Optimiza las consultas

Cuando creas y aplicas claves primarias y externas en tus tablas, BigQuery puede usar esa información para eliminar o optimizar ciertas uniones de consultas. Si bien es posible imitar estas optimizaciones reescribiendo tus consultas, estas reescrituras no siempre son prácticas.

En un entorno de producción, puedes crear vistas que unan muchas tablas de hechos y dimensiones. Los desarrolladores pueden consultar las vistas en lugar de consultar las tablas subyacentes y volver a escribir manualmente las uniones cada vez. Si defines las restricciones adecuadas, las optimizaciones de unión se producen automáticamente para cualquier búsqueda a la que se apliquen.

En los ejemplos de las siguientes secciones, se hace 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);

Elimina las uniones internas

Considera la siguiente búsqueda 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 primaria en la tabla customer, por lo que cada fila de la tabla store_sales tiene una sola coincidencia o ninguna si sales_customer es NULL. Dado que la consulta solo selecciona columnas de la tabla store_sales, el optimizador de consultas puede eliminar la unión y volver a escribir la consulta de la siguiente manera:

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

Elimina las uniones externas

Para quitar un LEFT OUTER JOIN, las claves de unión del lado derecho deben ser únicas y solo se deben seleccionar las columnas del lado izquierdo. Considera la siguiente búsqueda:

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

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

SELECT ss.*
FROM mydataset.store_sales;

Reordenar las uniones

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

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 sobre tablas con restricciones incumplidas pueden devolver resultados incorrectos.
  • Las claves primarias no pueden superar las 16 columnas.
  • Las claves externas deben tener valores que estén presentes en la columna de la tabla a la que se hace referencia. Estos valores pueden ser NULL.
  • Las claves primarias y las externas deben ser de los siguientes tipos: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING o TIMESTAMP.
  • Las claves primarias y las externas solo se pueden establecer en columnas de nivel superior.
  • No se pueden nombrar las claves primarias.
  • No se puede cambiar el nombre de las tablas con restricciones de clave primaria.
  • Una tabla puede tener hasta 64 claves externas.
  • Una clave externa no puede hacer referencia a una columna en la misma tabla.
  • Es posible que no se cambie el nombre ni el tipo de los campos que forman parte de restricciones de clave primaria o clave externa.
  • Si copias, clonas, restableces o creas una instantánea de una tabla sin la opción -a o --append_table, las restricciones de la tabla de origen se copian y se reemplazan en la tabla de destino. Si usas la opción -a o --append_table, solo los registros de la tabla de origen se agregan a la tabla de destino sin las restricciones de la tabla.

¿Qué sigue?