VALIDATE CONSTRAINT

On this page Carat arrow pointing down
Warning:
As of October 30, 2020, CockroachDB v19.1 is no longer supported. For more details, refer to the Release Support Policy.

The VALIDATE CONSTRAINT statement is part of ALTER TABLE and checks whether values in a column match a constraint on the column. This statement is especially useful after applying a constraint to an existing column via ADD CONSTRAINT. In this case, VALIDATE CONSTRAINT can be used to find values already in the column that do not match the constraint.

Tip:

New in v19.1: This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

Required privileges

The user must have the CREATE privilege on the table.

Synopsis

ALTER TABLE IF EXISTS table_name VALIDATE CONSTRAINT constraint_name

Parameters

Parameter Description
table_name The name of the table in which the constraint you'd like to validate lives.
constraint_name The name of the constraint on table_name you'd like to validate.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

In ADD CONSTRAINT, we added a foreign key constraint like so:

icon/buttons/copy
> ALTER TABLE orders ADD CONSTRAINT customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

In order to ensure that the data added to the orders table prior to the creation of the customer_fk constraint conforms to that constraint, run the following:

icon/buttons/copy
> ALTER TABLE orders VALIDATE CONSTRAINT customer_fk;
Note:

If present in a CREATE TABLE statement, the table is considered validated because an empty table trivially meets its constraints.

See also


Yes No
On this page

Yes No