---
title: "Primary Keys"
---

# 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

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

### Composite Primary Key

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

### Named Constraint

```sql
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:

```sql
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

```sql
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:

```sql
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

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

### Multi-Tenant Table

```sql
-- 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

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

### Natural Key

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

## Query Optimization

Primary keys enable efficient queries:

```sql
-- 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 with database branching:

```sql
-- 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:

```sql
-- 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:

```sql
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](https://www.heliosdb.com/docs/lite/user-guide/sql-reference/)
- [SQL Features](https://www.heliosdb.com/docs/lite/features/sql_features/)
- [Multi-Tenancy](https://www.heliosdb.com/docs/lite/features/multi-tenancy/)