Crear y administrar restricciones de verificación

Una restricción CHECK te permite especificar que los valores de una o más columnas deben satisfacer una expresión booleana. En este artículo, describiremos cómo administrar este tipo de restricción en la base de datos.

Agrega una restricción de verificación a una tabla nueva

En el siguiente fragmento de CREATE TABLE, creamos una tabla para almacenar información sobre conciertos. Para exigir que la hora de finalización de un cantante sea posterior a la hora de inicio, incluimos una restricción de verificación.

GoogleSQL 

CREATE TABLE Concerts (
  ConcertId INT64,
  StartTime Timestamp,
  EndTime Timestamp,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
) PRIMARY KEY (ConcertId);

PostgreSQL 

CREATE TABLE Concerts (
  ConcertId BIGINT,
  StartTime TIMESTAMPTZ,
  EndTime TIMESTAMPTZ,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
  PRIMARY KEY (ConcertId)
);

La definición de restricción comienza con la palabra clave CONSTRAINT. En este ejemplo, denominamos de forma explícita a la restricción como start_before_end, lo que facilita la identificación en los mensajes de error y siempre que sea necesario consultarlo. Si no se proporciona ningún nombre, Spanner lo proporciona, y el nombre generado comienza con el prefijo CK_. El nombre de la restricción se limita al esquema, junto con los nombres de las tablas y los índices, y debe ser único dentro del esquema. La definición de la restricción de verificación consiste en la palabra clave CHECK seguida de una expresión entre paréntesis. La expresión solo puede hacer referencia a columnas de esta tabla. En este ejemplo, se hace referencia a StartTime y EndTime, y la verificación aquí garantiza que la hora de inicio de un cantante siempre sea menor que la hora de finalización.

El valor de la expresión de restricción de verificación se evalúa cuando se inserta una fila nueva o cuando se actualizan StartTime o EndTime de una fila existente. Si la expresión se evalúa como TRUE o NULL, la restricción de verificación permite el cambio de datos. Si la expresión se evalúa como FALSE, no se permite el cambio de datos.

  • Las siguientes restricciones se aplican a una restricción de verificación de término expression.

    • La expresión solo puede hacer referencia a columnas en la misma tabla.

    • La expresión debe hacer referencia al menos a una columna no generada, ya sea directamente o a través de una columna generada que haga referencia a una columna no generada.

    • La expresión no puede hacer referencia a columnas que hayan establecido la opción allow_commit_timestamp.

    • La expresión no puede contener subconsultas.

    • La expresión no puede contener funciones no deterministas, como CURRENT_DATE() y CURRENT_TIMESTAMP().

Agrega una restricción de verificación a una tabla existente

Con la siguiente declaración ALTER TABLE, agregamos una restricción para asegurarnos de que todos los IDs de los conciertos sean mayores que cero.

ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);

Le asignamos un nombre a la restricción, concert_id_gt_0. Agregar una restricción CHECK a una tabla existente inicia la aplicación de la restricción de inmediato para los datos nuevos y comienza una operación de larga duración a fin de validar que los datos existentes se ajustan a la restricción nueva. Debido a que esta validación se realiza como una operación de larga duración, las transacciones en curso en la tabla no se ven afectadas. Para obtener más información, consulta Rendimiento de la actualización del esquema. Si hay algún incumplimiento en los datos existentes, la restricción se revierte.

Quita una restricción de verificación

La siguiente declaración DDL descarta una restricción CHECK de la tabla Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

Modifica una expresión de restricción de verificación

No se permite modificar la expresión de una restricción CHECK. En su lugar, debes descartar la restricción existente y crear una nueva con la expresión nueva.

Ver propiedades de una restricción de verificación

INFORMATION_SCHEMA de Spanner contiene información sobre las restricciones de verificación en tu base de datos. Estos son algunos ejemplos de las preguntas que puedes responder si consultas el esquema de información.

¿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 = 'CHECK';

¿Cuál es el estado actual de las restricciones de verificación de mi base de datos?

Si agregaste una restricción de verificación a una tabla existente, te recomendamos ver su estado actual para determinar, por ejemplo, si todos los datos existentes se validaron con la restricción. Si SPANNER_STATE muestra VALIDATING_DATA en la siguiente consulta, significa que Spanner aún está en proceso de validar los datos existentes en función de esa restricción.

SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;

¿Qué sigue?