VALIDATE CONSTRAINT

On this page Carat arrow pointing down

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