Primary Keys

Primary Keys

HeliosDB Nano 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

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

See Also

• SQL Reference
• SQL Features
• Multi-Tenancy