A CHECK
constraint allows you to specify that the values of one or more columns must
satisfy a boolean expression. In this article, we'll describe how to manage this
type of constraint in your database.
Add a check constraint to a new table
In the following CREATE TABLE
snippet, we create a table to store information
about concerts.
To require that the end time of a concert is later than its start time, we
include a check constraint.
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)
);
The constraint definition begins with the CONSTRAINT
keyword. We've explicitly
named the constraint as start_before_end
in this example, which makes it
easy to identify in error messages and whenever we need to refer to it. If no
name is given, Spanner provides one, with the generated name beginning
with the prefix CK_
. Constraint names are scoped to the schema, along with
the names for tables and indexes, and must be unique within the schema. The
check constraint definition consists of the keyword CHECK
followed by an expression in
parentheses. The expression can only reference columns of this table. In this
example, it references StartTime and EndTime, and the check here makes
sure that the start time of a concert is always less than the end time.
The value of the check constraint expression is evaluated when a new row is
inserted or when StartTime or EndTime of an existing row are updated. If
the expression evaluates to TRUE
or NULL
, the data change is allowed by the
check constraint. If the expression evaluates to FALSE
, the data change is not
allowed.
The following restrictions apply to a check constraint
expression
term.The expression can only reference columns in the same table.
The expression must reference at least one non-generated column, whether directly or through a generated column which references a non-generated column.
The expression can't reference columns that have set the
allow_commit_timestamp
option.The expression can't contain subqueries.
The expression can't contain non-deterministic functions, such as
CURRENT_DATE()
andCURRENT_TIMESTAMP()
.
Add a check constraint to an existing table
Using the following ALTER TABLE
statement, we add a constraint to make sure
that all concert ids are greater than zero.
ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);
Once again, we've given the constraint a name, concert_id_gt_0. Adding a
CHECK
constraint to an existing table starts the enforcement of the constraint
immediately for new data and starts a long-running operation to validate that
existing data conforms to the new constraint. Because this validation is
performed as a long-running operation, ongoing transactions on the table are not
impacted. For more information, see Schema update performance. If there are
any violations on existing data, the constraint is rolled back.
Remove a check constraint
The following DDL statement drops a CHECK
constraint from the Concerts
table.
ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;
Modify a check constraint expression
Modifying the expression of a CHECK
constraint is not allowed. Instead, you
need to drop the existing constraint and create a new constraint with the new
expression.
View properties of a check constraint
Spanner's INFORMATION_SCHEMA contains information about the check constraints on your database. The following are some examples of the questions you can answer by querying the information schema.
What check constraints are defined in my database?
SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'CHECK';
What is the current state of the check constraints in my database?
If you have added a check constraint to an existing table, you might want to
view its current state to determine, for example, whether all existing data has
been validated against the constraint. If SPANNER_STATE
returns
VALIDATING_DATA
in the following query, it means Spanner
is still in the process of validating existing data against that constraint.
SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;
What's next
- Learn more about Spanner's INFORMATION SCHEMA.