Constraint Reference

Horizon Epoch versions database constraints alongside your data, enabling schema evolution with the same Git-like branching, merging, and conflict detection you use for data.

Constraint Types

Horizon Epoch supports seven constraint types that map to PostgreSQL DDL:

Foreign Key Constraints

Foreign keys define referential integrity between tables.

Structure

#![allow(unused)]
fn main() {
ForeignKeyConstraint {
    name: String,                    // Constraint name
    columns: Vec<String>,            // Local columns
    references_table: String,        // Referenced table
    references_columns: Vec<String>, // Referenced columns
    on_update: ReferentialAction,    // Action on parent update
    on_delete: ReferentialAction,    // Action on parent delete
    deferrable: bool,                // Can be deferred
    initially_deferred: bool,        // Deferred by default
}
}

Referential Actions

PostgreSQL Mapping

-- Generated DDL
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE CASCADE ON UPDATE NO ACTION;

Python Example

from horizon_epoch import Schema, ForeignKeyConstraint

schema = Schema.builder() \
    .add_field("customer_id", "integer") \
    .add_foreign_key(
        name="fk_orders_customer",
        columns=["customer_id"],
        references_table="customers",
        references_columns=["id"],
        on_delete="cascade"
    ) \
    .build()

Unique Constraints

Unique constraints ensure no duplicate values exist in the specified columns.

Structure

#![allow(unused)]
fn main() {
UniqueConstraint {
    name: String,               // Constraint name
    columns: Vec<String>,       // Columns in the constraint
    nulls_distinct: bool,       // PostgreSQL 15+ NULLS NOT DISTINCT
    deferrable: bool,           // Can be deferred
    initially_deferred: bool,   // Deferred by default
    index_method: Option<String>, // Index method (btree, hash)
}
}

PostgreSQL Mapping

-- Simple unique constraint
ALTER TABLE users ADD CONSTRAINT uq_users_email
    UNIQUE (email);

-- Composite unique constraint
ALTER TABLE order_items ADD CONSTRAINT uq_order_product
    UNIQUE (order_id, product_id);

-- PostgreSQL 15+ with NULLS NOT DISTINCT
ALTER TABLE users ADD CONSTRAINT uq_users_phone
    UNIQUE NULLS NOT DISTINCT (phone);

Python Example

schema = Schema.builder() \
    .add_field("email", "text") \
    .add_unique_constraint(
        name="uq_users_email",
        columns=["email"]
    ) \
    .build()

Check Constraints

Check constraints validate that column values satisfy a boolean expression.

Structure

#![allow(unused)]
fn main() {
CheckConstraint {
    name: String,                      // Constraint name
    expression: String,                // SQL boolean expression
    referenced_columns: Vec<String>,   // Columns used in expression
    is_not_null: bool,                 // Is this a NOT NULL constraint?
    no_inherit: bool,                  // PostgreSQL NO INHERIT option
}
}

PostgreSQL Mapping

-- Value range check
ALTER TABLE products ADD CONSTRAINT chk_products_price
    CHECK (price > 0);

-- Pattern check
ALTER TABLE users ADD CONSTRAINT chk_users_email
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- NOT NULL (special case)
ALTER TABLE users ALTER COLUMN name SET NOT NULL;

Python Example

schema = Schema.builder() \
    .add_field("price", "numeric") \
    .add_check_constraint(
        name="chk_products_price",
        expression="price > 0"
    ) \
    .build()

Exclusion Constraints

Exclusion constraints prevent overlapping or conflicting values using operators.

Structure

#![allow(unused)]
fn main() {
ExclusionConstraint {
    name: String,                      // Constraint name
    index_method: String,              // Usually "gist" or "btree"
    columns: Vec<String>,              // Columns for display
    elements: Vec<ExclusionElement>,   // Column/operator pairs
    where_clause: Option<String>,      // Partial exclusion
    deferrable: bool,                  // Can be deferred
    initially_deferred: bool,          // Deferred by default
}

ExclusionElement {
    column: String,          // Column name or expression
    operator: String,        // Comparison operator
    opclass: Option<String>, // Operator class
}
}

PostgreSQL Mapping

-- Prevent overlapping time ranges
ALTER TABLE reservations ADD CONSTRAINT excl_reservations_overlap
    EXCLUDE USING gist (
        room_id WITH =,
        tsrange(start_time, end_time) WITH &&
    );

-- Prevent overlapping circles (geometric)
ALTER TABLE zones ADD CONSTRAINT excl_zones_overlap
    EXCLUDE USING gist (area WITH &&);

Python Example

schema = Schema.builder() \
    .add_field("room_id", "integer") \
    .add_field("start_time", "timestamp") \
    .add_field("end_time", "timestamp") \
    .add_exclusion_constraint(
        name="excl_reservations_overlap",
        index_method="gist",
        elements=[
            {"column": "room_id", "operator": "="},
            {"column": "tsrange(start_time, end_time)", "operator": "&&"}
        ]
    ) \
    .build()

Index Definitions

Indexes improve query performance and can enforce uniqueness.

Structure

#![allow(unused)]
fn main() {
IndexDefinition {
    name: String,                        // Index name
    method: IndexMethod,                 // btree, hash, gist, gin, brin
    columns: Vec<IndexColumn>,           // Columns with sort options
    is_unique: bool,                     // UNIQUE index
    where_clause: Option<String>,        // Partial index
    include_columns: Vec<String>,        // Covering index (INCLUDE)
    storage_parameters: HashMap<String, String>, // fillfactor, etc.
    concurrent: bool,                    // CONCURRENTLY option
    if_not_exists: bool,                 // IF NOT EXISTS
}

IndexColumn {
    name: String,                    // Column name or expression
    sort_order: Option<SortOrder>,   // ASC or DESC
    nulls_order: Option<NullsOrder>, // NULLS FIRST or NULLS LAST
    opclass: Option<String>,         // Operator class
}
}

Index Methods

PostgreSQL Mapping

-- Basic index
CREATE INDEX idx_users_email ON users (email);

-- Composite index with sort order
CREATE INDEX idx_orders_date_desc ON orders (created_at DESC NULLS LAST);

-- Partial index
CREATE INDEX idx_orders_pending ON orders (status)
    WHERE status = 'pending';

-- Covering index (PostgreSQL 11+)
CREATE INDEX idx_users_email_include ON users (email)
    INCLUDE (name, created_at);

-- GIN index for JSONB
CREATE INDEX idx_products_attrs ON products USING gin (attributes);

Python Example

schema = Schema.builder() \
    .add_field("email", "text") \
    .add_index(
        name="idx_users_email",
        columns=[{"name": "email"}],
        method="btree"
    ) \
    .build()

Enum Types

Enum types define a fixed set of allowed values.

Structure

#![allow(unused)]
fn main() {
EnumType {
    name: String,           // Type name
    values: Vec<String>,    // Ordered enum values
    schema: Option<String>, // Schema name (optional)
}
}

PostgreSQL Mapping

-- Create enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');

-- Add value to enum (PostgreSQL is additive-only for enums)
ALTER TYPE order_status ADD VALUE 'cancelled' AFTER 'delivered';

-- Use in table
ALTER TABLE orders ALTER COLUMN status TYPE order_status
    USING status::order_status;

Enum Diffing

When comparing enum types between branches, Horizon Epoch tracks:

• Added values: Safe to add in migrations
• Removed values: Requires data migration (destructive)
• Reordered values: May affect comparisons

# Check if enum change is safe (additive only)
enum_diff = client.diff_enums("main", "feature-branch")
if enum_diff.is_additive():
    print("Safe to merge - only new values added")
else:
    print("Warning: Enum values removed or reordered")

Python Example

schema = Schema.builder() \
    .add_enum_type(
        name="order_status",
        values=["pending", "processing", "shipped", "delivered"]
    ) \
    .add_field("status", "order_status") \
    .build()

Default Values

Default values specify what value to use when none is provided.

Structure

#![allow(unused)]
fn main() {
enum DefaultValue {
    Literal(String),      // Literal value: 'active', 42, true
    Function(String),     // Function call: gen_random_uuid()
    Sequence(String),     // Sequence: nextval('users_id_seq')
    Expression(String),   // Expression: CURRENT_DATE + INTERVAL '30 days'
    CurrentTimestamp,     // CURRENT_TIMESTAMP / NOW()
    CurrentDate,          // CURRENT_DATE
    CurrentUser,          // CURRENT_USER
    Null,                 // NULL (explicit)
}
}

PostgreSQL Mapping

-- Literal default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Function default
ALTER TABLE users ALTER COLUMN id SET DEFAULT gen_random_uuid();

-- Timestamp default
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

-- Expression default
ALTER TABLE subscriptions ALTER COLUMN expires_at
    SET DEFAULT CURRENT_DATE + INTERVAL '30 days';

Determinism

Horizon Epoch tracks whether defaults are deterministic:

Storage Backend Support

Different storage backends support constraints at different levels:

Constraint Support Matrix

* SQLite requires PRAGMA foreign_keys = ON to enforce foreign keys.

PostgreSQL (Full Support)

PostgreSQL provides ConstraintSupportLevel::Full:

MySQL (Full Support)

MySQL provides ConstraintSupportLevel::Full:

SQL Server (Full Support)

SQL Server provides ConstraintSupportLevel::Full:

SQLite (Partial Support)

SQLite provides ConstraintSupportLevel::Partial:

S3/Delta Lake (Metadata Only)

S3/Delta Lake provides ConstraintSupportLevel::MetadataOnly:

Azure Blob Storage (Metadata Only)

Azure Blob Storage provides ConstraintSupportLevel::MetadataOnly:

Google Cloud Storage (Metadata Only)

GCS provides ConstraintSupportLevel::MetadataOnly:

Local Filesystem (Metadata Only)

Local filesystem provides ConstraintSupportLevel::MetadataOnly:

ConstraintSupportLevel Enum

#![allow(unused)]
fn main() {
pub enum ConstraintSupportLevel {
    Full,          // All constraints enforced (PostgreSQL)
    MetadataOnly,  // Constraints stored but not enforced (S3)
    Partial,       // Some constraints work, others don't
    None,          // No constraint support
}

impl ConstraintSupportLevel {
    pub fn can_enforce(&self) -> bool;      // True only for Full
    pub fn can_store(&self) -> bool;        // True for Full, MetadataOnly, Partial
    pub fn can_execute_ddl(&self) -> bool;  // True only for Full
}
}

Cross-Backend Considerations

When working with mixed storage backends:

1. Constraints are always stored in metadata - Even if not enforced, constraints are tracked
2. DDL generation targets PostgreSQL - Use PostgresDdlGenerator for migrations
3. Merge conflicts work everywhere - Constraint conflicts are detected regardless of enforcement
4. Validation is backend-specific - Check can_enforce() before relying on constraint enforcement

TableConstraints Container

All constraints for a table are grouped in TableConstraints:

#![allow(unused)]
fn main() {
pub struct TableConstraints {
    pub foreign_keys: Vec<ForeignKeyConstraint>,
    pub unique_constraints: Vec<UniqueConstraint>,
    pub check_constraints: Vec<CheckConstraint>,
    pub exclusion_constraints: Vec<ExclusionConstraint>,
    pub indexes: Vec<IndexDefinition>,
    pub enum_types: Vec<EnumType>,
}
}

Key Methods

#![allow(unused)]
fn main() {
// Get all constraints as enum variants
let all: Vec<Constraint> = constraints.all_constraints();

// Find constraint by name
let fk: Option<&ForeignKeyConstraint> = constraints.find_foreign_key("fk_orders_customer");

// Get constraints affecting a column
let affecting: Vec<&Constraint> = constraints.constraints_on_column("customer_id");

// Validate all constraints
let errors: Vec<String> = constraints.validate();
}

See Also

• Schema Versioning Tutorial - Step-by-step guide
• Merge Algorithm - How constraint conflicts are resolved
• Configuration Reference - DDL generation options