Foreign Keys¶

HeliosDB-Lite supports FOREIGN KEY constraints for referential integrity between tables.

Overview¶

Foreign keys provide:

Referential Integrity: Ensure relationships between tables are valid
Cascading Actions: Automatic updates/deletes of related rows
Data Consistency: Prevent orphaned records
Flexible Enforcement: IMMEDIATE, DEFERRED, or LOCK-FREE modes

Syntax¶

Basic Foreign Key¶

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total DECIMAL
);

Named Foreign Key Constraint¶

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

Composite Foreign Key¶

CREATE TABLE line_items (
    order_id INT,
    line_number INT,
    warehouse_id INT,
    location_id INT,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (warehouse_id, location_id)
        REFERENCES locations(warehouse_id, location_id)
);

Referential Actions¶

ON DELETE Actions¶

Action	Description
`NO ACTION`	Reject delete if references exist (default)
`RESTRICT`	Same as NO ACTION, checked immediately
`CASCADE`	Delete referencing rows
`SET NULL`	Set foreign key columns to NULL
`SET DEFAULT`	Set foreign key columns to default value

ON UPDATE Actions¶

Action	Description
`NO ACTION`	Reject update if references exist (default)
`RESTRICT`	Same as NO ACTION, checked immediately
`CASCADE`	Update referencing rows
`SET NULL`	Set foreign key columns to NULL
`SET DEFAULT`	Set foreign key columns to default value

Examples¶

-- Cascade deletes
CREATE TABLE comments (
    id INT PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    content TEXT
);

-- Set NULL on delete
CREATE TABLE employees (
    id INT PRIMARY KEY,
    manager_id INT REFERENCES employees(id) ON DELETE SET NULL,
    name TEXT
);

-- Cascade updates
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT REFERENCES orders(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    product_id INT REFERENCES products(id) ON DELETE RESTRICT
);

Enforcement Modes¶

HeliosDB-Lite supports three constraint enforcement modes:

IMMEDIATE (Default)¶

Constraint checked immediately on each statement:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id)
    -- Constraint checked on every INSERT/UPDATE
);

DEFERRED¶

Constraint checked at transaction COMMIT:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- Allows circular references within a transaction
BEGIN;
INSERT INTO orders VALUES (1, 100);  -- customer 100 doesn't exist yet
INSERT INTO customers VALUES (100, 'Alice');  -- now it does
COMMIT;  -- constraint checked here

LOCK-FREE¶

Async validation for bulk operations (eventual consistency):

-- Set enforcement mode via SQL setting
SET constraint_enforcement = 'lock_free';

-- Bulk insert without blocking on FK validation
INSERT INTO orders SELECT * FROM staging_orders;

-- Constraints validated asynchronously

Viewing Foreign Keys¶

System Views¶

-- List all foreign key constraints
SELECT * FROM pg_foreign_keys;

-- View constraints for a specific table
SELECT
    constraint_name,
    table_name,
    column_name,
    references_table,
    references_column,
    on_delete,
    on_update
FROM pg_foreign_keys
WHERE table_name = 'orders';

REPL Commands¶

\d orders           -- Shows table with FK constraints
\dt+ orders         -- Detailed table info including FKs

Managing Foreign Keys¶

Add Foreign Key (ALTER TABLE)¶

-- Add FK to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Drop Foreign Key¶

-- Drop FK constraint
ALTER TABLE orders DROP CONSTRAINT fk_customer;

Integration with Other Features¶

Branching¶

Foreign keys work seamlessly with database branching:

-- Create branch
CREATE BRANCH dev FROM main AS OF NOW;

-- FK constraints are preserved in the branch
INSERT INTO orders VALUES (1, 999);  -- Fails if customer 999 doesn't exist

Time-Travel¶

Historical queries respect FK relationships:

-- Query historical data with FK joins
SELECT o.*, c.name
FROM orders AS OF TIMESTAMP '2025-01-01' o
JOIN customers AS OF TIMESTAMP '2025-01-01' c ON o.customer_id = c.id;

Multi-Tenancy¶

Foreign keys work with tenant isolation:

-- FK within tenant scope
CREATE TABLE tenant_orders (
    id INT,
    tenant_id INT,
    customer_id INT,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, customer_id)
        REFERENCES tenant_customers(tenant_id, id)
);

Best Practices¶

Always define FKs: Use foreign keys to enforce data relationships
Choose appropriate actions: Use CASCADE carefully, RESTRICT for safety
Index FK columns: Improves JOIN performance and FK validation
Consider deferrable: Use for complex transactions with circular references
Use naming conventions: fk_<table>_<column>__<ref_table>

Limitations¶

Self-referencing FKs supported (e.g., employee → manager)
Maximum 16 columns per composite FK
Referenced columns must have PRIMARY KEY or UNIQUE constraint