CHECK Constraints and Table Properties Lifecycle

Declare CHECK constraints inside TBLPROPERTIES and prove they survive every UPDATE, DELETE, and re-INSERT -- not just the initial load.

Category: delta

Syntax

-- SETUP
CREATE ZONE IF NOT EXISTS demo_zone TYPE EXTERNAL
    COMMENT 'External and Delta tables -- demo datasets';

CREATE SCHEMA IF NOT EXISTS demo_zone.delta_demos
    COMMENT 'Delta table management tutorial demos';

CREATE DELTA TABLE IF NOT EXISTS demo_zone.delta_demos.products (
    id        INT,
    name      VARCHAR,
    category  VARCHAR,
    price     DOUBLE,
    stock     INT,
    discount  DOUBLE
) LOCATION '~/delta-data/products'
TBLPROPERTIES (
    'delta.enableDeletionVectors' = 'true',
    'delta.constraints.price_positive' = 'price > 0',
    'delta.constraints.stock_non_negative' = 'stock >= 0',
    'delta.constraints.discount_non_negative' = 'discount >= 0'
);

INSERT INTO demo_zone.delta_demos.products VALUES
    (1,  'Widget A',     'Tools',       25.00,  100, 0.00),
    (2,  'Widget B',     'Tools',       30.00,  80,  5.00),
    (3,  'Gadget X',     'Electronics', 150.00, 50,  10.00),
    (4,  'Gadget Y',     'Electronics', 200.00, 30,  0.00),
    (5,  'Book Alpha',   'Books',       20.00,  200, 0.00),
    (6,  'Book Beta',    'Books',       35.00,  150, 15.00),
    (7,  'Shirt Red',    'Clothing',    40.00,  75,  20.00),
    (8,  'Shirt Blue',   'Clothing',    40.00,  60,  0.00),
    (9,  'Lamp Basic',   'Home',        45.00,  40,  5.00),
    (10, 'Lamp Pro',     'Home',        90.00,  25,  10.00),
    (11, 'Cable USB',    'Electronics', 10.00,  500, 0.00),
    (12, 'Cable HDMI',   'Electronics', 15.00,  300, 0.00),
    (13, 'Mug Plain',    'Home',        12.00,  200, 0.00),
    (14, 'Mug Fancy',    'Home',        18.00,  100, 5.00),
    (15, 'Pen Set',      'Office',      8.00,   400, 0.00),
    (16, 'Notebook',     'Office',      12.00,  250, 10.00),
    (17, 'Bag Small',    'Clothing',    55.00,  45,  0.00),
    (18, 'Bag Large',    'Clothing',    85.00,  20,  25.00),
    (19, 'Tool Kit',     'Tools',       60.00,  35,  0.00),
    (20, 'Tool Pro',     'Tools',       120.00, 15,  10.00);

-- QUERIES
-- Baseline invariant check -- zero violations
SELECT
    COUNT(*) AS total_products,
    COUNT(*) FILTER (WHERE price <= 0) AS price_violations,
    COUNT(*) FILTER (WHERE stock < 0) AS stock_violations,
    COUNT(*) FILTER (WHERE discount < 0) AS discount_violations
FROM demo_zone.delta_demos.products;

-- Pre-UPDATE summary
SELECT category,
       COUNT(*) AS item_count,
       ROUND(SUM(price), 2) AS total_price,
       ROUND(AVG(price), 2) AS avg_price
FROM demo_zone.delta_demos.products
GROUP BY category
ORDER BY total_price DESC;

-- Bulk UPDATE: 10% price increase -- preserves price > 0 inherently
UPDATE demo_zone.delta_demos.products
SET price = ROUND(price * 1.10, 2);

-- Re-validate after the bulk UPDATE
SELECT
    COUNT(*) AS total_products,
    COUNT(*) FILTER (WHERE price <= 0) AS price_violations,
    COUNT(*) FILTER (WHERE stock < 0) AS stock_violations,
    COUNT(*) FILTER (WHERE discount < 0) AS discount_violations
FROM demo_zone.delta_demos.products;

-- Boundary: setting stock = 0 is valid because constraint is stock >= 0
UPDATE demo_zone.delta_demos.products
SET stock = 0
WHERE id IN (2, 8, 14);

-- Confirm the 3 discontinued items
SELECT COUNT(*) AS zero_stock_items
FROM demo_zone.delta_demos.products
WHERE stock = 0;

-- DELETE the discontinued items -- DV removes them logically; constraints untouched
DELETE FROM demo_zone.delta_demos.products
WHERE stock = 0;

-- Final invariant check -- all three constraints still hold
SELECT
    COUNT(*) AS total_products,
    COUNT(*) FILTER (WHERE price <= 0) AS price_violations,
    COUNT(*) FILTER (WHERE stock < 0) AS stock_violations,
    COUNT(*) FILTER (WHERE discount < 0) AS discount_violations
FROM demo_zone.delta_demos.products;

-- Post-DML category summary
SELECT category,
       COUNT(*) AS item_count,
       ROUND(SUM(price), 2) AS total_price,
       ROUND(AVG(price), 2) AS avg_price
FROM demo_zone.delta_demos.products
GROUP BY category
ORDER BY total_price DESC;

-- CLEANUP
DROP DELTA TABLE IF EXISTS demo_zone.delta_demos.products WITH FILES;
DROP SCHEMA IF EXISTS demo_zone.delta_demos;
DROP ZONE IF EXISTS demo_zone;

Description

## When to Use Whenever an invariant is a property of the data itself rather than of the ingesting job. Examples: price > 0, stock >= 0, end_date >= start_date, status IN ('active','closed'). Delta stores CHECK constraints as expressions in the transaction log. Every writer -- including UPDATE and MERGE statements from other jobs -- must evaluate the constraint before committing, so the invariant cannot be bypassed by a rogue query. This demo creates three CHECK constraints at table-creation time and walks through a full DML lifecycle (bulk UPDATE, boundary UPDATE, DELETE) re-verifying after each step. ## What You Will Learn 1. How to declare CHECK constraints inline in CREATE DELTA TABLE via TBLPROPERTIES ('delta.constraints.<name>' = '<expr>') 2. How constraints are evaluated by every writer -- any failing row aborts the entire transaction 3. The boundary semantics of >= vs > (stock >= 0 allows 0 for "discontinued"; stock > 0 would not) 4. That constraints survive bulk UPDATEs (10% price increase) and DELETE statements automatically 5. How to express invariants that are inherently preserved by the mutation (multiplying a positive by 1.10 cannot produce a non-positive) 6. How to re-validate invariants programmatically with COUNT(*) FILTER (WHERE <invariant_violated>) after every step 7. How deletion vectors (enableDeletionVectors = 'true') interact with constraints -- they do not change the constraint contract, only the physical delete mechanism ## Prerequisites None. The setup creates a 20-row products table with three CHECK constraints and deletion vectors enabled.

Parameters

Name	Type	Description
`TABLE_PATH`		Filesystem or object-store location for the Delta table.

Pitfalls

CHECK constraints are strict boolean predicates. A NULL-producing expression evaluates to NULL, which Delta treats as PASS (same as standard SQL CHECK). If you want to reject NULLs, write stock IS NOT NULL AND stock >= 0 or add a NOT NULL column constraint separately.
Adding a CHECK constraint to an existing table via ALTER TABLE ADD CONSTRAINT scans every file to validate current rows. On large tables this can be expensive -- plan the backfill first.
The property key must be delta.constraints.<name> -- the <name> part is the human-readable constraint identifier you will see in error messages and must be unique per table.
Constraints do not fire on READ, only on WRITE. If you disable the property (ALTER TABLE UNSET TBLPROPERTIES 'delta.constraints.price_positive') and then write a violating row, Delta will accept it; re-adding the constraint later will then fail the validation scan.
Generated columns (GENERATED ALWAYS AS (expr)) require delta.feature.allowColumnDefaults/ generatedColumns table features and are a separate mechanism from CHECK. They are computed at write time and cannot be INSERTed explicitly.
Deletion vectors + CHECK constraints compose cleanly: DV marks rows deleted without rewriting, and the constraint scans only the still-live rows on subsequent writes.

Open in interactive docs → DeltaForge home →