Primary Keys¶

HeliosDB-Lite supports PRIMARY KEY constraints for unique row identification and efficient data access.

Overview¶

Primary keys provide:

Uniqueness: Guarantee no duplicate rows
Not Null: Implicitly enforce NOT NULL constraint
Fast Lookups: Automatic index creation for O(log n) access
Data Integrity: Foundation for relationships and joins

Syntax¶

Single-Column Primary Key¶

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT,
    email TEXT
);

Composite Primary Key¶

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Named Constraint¶

CREATE TABLE products (
    sku VARCHAR(20),
    name TEXT,
    CONSTRAINT pk_products PRIMARY KEY (sku)
);

Behavior¶

Automatic NOT NULL¶

Primary key columns are automatically NOT NULL:

CREATE TABLE test (id INT PRIMARY KEY, name TEXT);

-- This fails with constraint violation
INSERT INTO test (id, name) VALUES (NULL, 'Alice');
-- Error: NULL value not allowed in PRIMARY KEY column

Uniqueness Enforcement¶

CREATE TABLE users (id INT PRIMARY KEY, name TEXT);

INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (1, 'Bob');  -- Error: duplicate key

Index Creation¶

Primary keys automatically create an index:

CREATE TABLE products (id INT PRIMARY KEY, name TEXT);

-- Equivalent to:
-- CREATE TABLE products (id INT, name TEXT);
-- CREATE UNIQUE INDEX pk_products ON products(id);

Primary Key Selection¶

Good Primary Key Candidates¶

Type	Example	Use Case
Auto-increment	`SERIAL`, `BIGSERIAL`	Most tables
UUID	`UUID DEFAULT gen_random_uuid()`	Distributed systems
Natural key	`email`, `sku`	When unique and stable
Composite	`(tenant_id, user_id)`	Multi-tenant applications

Poor Primary Key Choices¶

Frequently changing values
Long text strings
Nullable columns
Floating-point numbers

Examples¶

Users Table¶

CREATE TABLE users (
    id INT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Multi-Tenant Table¶

-- Composite PK for tenant isolation
CREATE TABLE tenant_users (
    tenant_id INT,
    user_id INT,
    name TEXT,
    PRIMARY KEY (tenant_id, user_id)
);

UUID Primary Key¶

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Natural Key¶

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,  -- ISO country code
    name TEXT NOT NULL,
    population BIGINT
);

Query Optimization¶

Primary keys enable efficient queries:

-- O(log n) lookup by primary key
SELECT * FROM users WHERE id = 123;

-- Range scan on primary key
SELECT * FROM users WHERE id BETWEEN 100 AND 200;

-- Join optimization using primary key
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';

Integration with Other Features¶

Branching¶

Primary keys work seamlessly with database branching:

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

-- Primary key constraints are preserved
INSERT INTO users VALUES (1, 'Alice');  -- Works on dev branch

Time-Travel¶

Primary key lookups support time-travel queries:

-- Lookup as of specific timestamp
SELECT * FROM users
AS OF TIMESTAMP '2025-01-01 00:00:00'
WHERE id = 123;

Vector Tables¶

Combine primary keys with vector search:

CREATE TABLE documents (
    id INT PRIMARY KEY,
    title TEXT,
    embedding VECTOR(768)
);

CREATE INDEX ON documents USING hnsw (embedding);

Constraints vs Indexes¶

Feature	Primary Key	Unique Index
NULL allowed	No	Yes (one NULL)
Multiple per table	No (one only)	Yes
Implicit index	Yes	Yes
Semantic meaning	Identifies row	Just enforces uniqueness

Current Limitations¶

No SERIAL/BIGSERIAL auto-increment (use application-generated IDs)
No ALTER TABLE ... ADD PRIMARY KEY (define at creation time)
Foreign key constraints not yet enforced

Best Practices¶

Always define a primary key - Every table should have one
Use simple types - INT or UUID preferred
Keep it stable - Don't use values that change
Consider composite keys - For junction/mapping tables
Use meaningful names - pk_tablename convention