Usa claves primarias y externas

Las claves primarias y externas son restricciones de tablas que pueden ayudar con la optimización de 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: Es una combinación de una o más columnas que es única para cada fila y no es NULL.
  • Clave externa: Es una combinación de una o más columnas que está presente en la columna de clave primaria de una tabla a la que se hace referencia o es NULL.

Por lo general, las claves primarias y externas se usan para garantizar la integridad de los datos y habilitar la optimización de consultas. BigQuery no aplica restricciones de claves primarias y externas. Cuando declaras restricciones en tus tablas, debes asegurarte de que tus datos las cumplan. Para garantizar la unicidad de los valores dentro de una columna, considera usar una columna de identidad. BigQuery puede usar restricciones de tablas 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 tablas a través de la API de BigQuery actualizando el TableConstraints objeto.

Ver restricciones

Las siguientes vistas te brindan información sobre las restricciones de tus tablas:

  • La vista INFORMATION_SCHEMA.TABLE_CONSTRAINTS contiene información sobre todas las restricciones de claves primarias y externas en las tablas dentro de un conjunto de datos.
  • La vista INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contiene información sobre las columnas de clave primaria de cada tabla y las columnas a las que hacen referencia las claves externas de otras tablas dentro de un conjunto de datos.
  • La vista INFORMATION_SCHEMA.KEY_COLUMN_USAGE contiene información sobre las columnas de cada tabla que están restringidas como claves primarias o externas.

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 reescribir manualmente las uniones cada vez. Si defines las restricciones adecuadas, las optimizaciones de unión se realizan automáticamente para cualquier consulta 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 consulta que contiene una 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. Como la consulta solo selecciona columnas de la tabla store_sales, el optimizador de consultas puede eliminar la unión y reescribir la consulta de la siguiente manera:

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

Elimina las uniones externas

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

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 se puede eliminar la LEFT OUTER JOIN:

SELECT ss.*
FROM mydataset.store_sales;

Reordena las uniones

Cuando BigQuery no puede eliminar una unión, puede usar restricciones de tablas para obtener información sobre las cardinalidades de 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. Es posible que las consultas sobre tablas con restricciones incumplidas muestren resultados incorrectos.
  • Las claves primarias no pueden exceder 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 externas deben ser de uno de los siguientes tipos: BIGNUMERIC, BOOLEAN, BYTES, DATE, DATETIME, INT64, NUMERIC, STRING, o TIMESTAMP.
  • Las claves primarias y las externas solo se pueden establecer en columnas de nivel superior.
  • Las claves primarias no pueden tener nombres.
  • 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 de la misma tabla.
  • No se puede cambiar el nombre de los campos que forman parte de las restricciones de clave primaria o clave externa, ni tampoco su tipo.
  • Si copias, clonas, restauras, o crea 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 -a o --append_table opción, solo los registros de la tabla de origen se agregan a la tabla de destino sin las restricciones de la tabla.

¿Qué sigue?