How to VALIDATE CONSTRAINT in BigQuery

How do I validate existing data against a constraint in BigQuery?

VALIDATE CONSTRAINT checks all existing rows against a previously-defined CHECK or NOT NULL constraint and returns any violations.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does VALIDATE CONSTRAINT do?

VALIDATE CONSTRAINT scans every row in a table and evaluates it against a specific CHECK or NOT NULL constraint that was added with NOT ENFORCED. The statement reports whether the data passes and, if it fails, lists the first 1,000 violations.

When should I run VALIDATE CONSTRAINT?

Run it after bulk-loading historical data or changing business rules. First create the constraint as NOT ENFORCED, backfill or clean data, then validate.If the command returns zero violations, you can safely switch the constraint to ENFORCED.

How do I write the syntax?

Use ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name. Optionally, add OPTIONS(max_rows=INT64, return_summary=BOOL). See the full syntax below.

Does validation lock the table?

No. VALIDATE CONSTRAINT is online; queries can read and write while validation runs, but heavy tables may experience higher slot usage.

Can I validate multiple constraints at once?

You must run one statement per constraint.Automate with scripting if many constraints exist.

Practical example

Suppose Orders.total_amount must be non-negative. You add the constraint as NOT ENFORCED, load old orders, and then validate:

-- Step 1: Add but don’t enforce ALTER TABLE Orders ADD CONSTRAINT chk_total_amount CHECK (total_amount >= 0) NOT ENFORCED;. -- Step 2: Validate existing rows ALTER TABLE Orders VALIDATE CONSTRAINT chk_total_amount; -- Step 3: Enforce going forward ALTER TABLE Orders ALTER CONSTRAINT chk_total_amount ENFORCED;

Best practices for production data

Add constraints as NOT ENFORCED in staging first, fix bad rows using UPDATE … WHERE NOT condition, then validate in production. Monitor INFORMATION_SCHEMA.CONSTRAINT_VALIDATION_RESULTS to audit failures.

Common mistakes and fixes

Mistake 1: Adding an ENFORCED constraint before cleaning legacy data. Fix: Use NOT ENFORCED, clean, validate, then enforce.

Mistake 2: Assuming VALIDATE automatically turns the constraint on.Fix: After a successful validation, issue ALTER CONSTRAINT … ENFORCED.

Need a quick checklist?

Add NOT ENFORCED constraint.
Load or clean data.
Run VALIDATE CONSTRAINT.
If passed, ALTER … ENFORCED.

Further learning

Explore INFORMATION_SCHEMA views, automate checks with scheduled queries, and integrate with CI pipelines for continuous data quality.

Why How to VALIDATE CONSTRAINT in BigQuery is important

How to VALIDATE CONSTRAINT in BigQuery Example Usage


-- Goal: ensure every Product has non-negative stock before enforcing
ALTER TABLE Products
ADD CONSTRAINT chk_stock_non_negative
CHECK (stock >= 0) NOT ENFORCED;

-- Backfill bad data (optional)
UPDATE Products SET stock = 0 WHERE stock < 0;

-- Validate existing rows
ALTER TABLE Products
VALIDATE CONSTRAINT chk_stock_non_negative;

-- Enforce for future inserts/updates
ALTER TABLE Products
ALTER CONSTRAINT chk_stock_non_negative ENFORCED;

How to VALIDATE CONSTRAINT in BigQuery Syntax


ALTER TABLE [IF EXISTS] project.dataset.table_name
VALIDATE CONSTRAINT constraint_name
[OPTIONS (
  max_rows = int64,           -- default 1000 rows returned
  return_summary = BOOL       -- default TRUE, show pass/fail only
)];

-- Ecommerce example 1: validate NOT NULL on Customers.email
ALTER TABLE Customers
VALIDATE CONSTRAINT nn_customer_email;

-- Ecommerce example 2: validate CHECK on OrderItems.quantity > 0
ALTER TABLE OrderItems
VALIDATE CONSTRAINT chk_quantity_positive;

Common Mistakes

Running VALIDATE CONSTRAINT before cleaning legacy rows. This returns violations and aborts enforcement. Clean or correct data first, then re-run validation.
Believing validation enforces the rule automatically. After a successful VALIDATE, you still must issue ALTER TABLE ... ALTER CONSTRAINT ... ENFORCED to reject future bad rows.