Skip to content
Coordinator DB
View as Markdown
View as Markdown

Choose a primary key ​

A primary key identifies a row. Choose values that remain stable when other row fields change, and make additional business identifiers UNIQUE when they also need duplicate protection.

Ordinary integer identity ​

In an ordinary rowid table, a column declared exactly INTEGER PRIMARY KEY aliases the row identifier. Other native integer declarations, such as UInt64 PRIMARY KEY, do not have that alias contract.

Use UNIQUE constraints or unique indexes for additional keys. These constraints enforce uniqueness; a ClickHouse sorting-key or sparse-index contract does not apply.

Compound identity ​

A table-level PRIMARY KEY can contain several columns. A WITHOUT ROWID table uses that declared key as its row identity:

sql
CREATE TABLE readings
(
    sensor UInt32,
    day Date32,
    value Float64,
    PRIMARY KEY(sensor, day)
) WITHOUT ROWID;
INSERT INTO readings VALUES (7, '2025-03-05', 12.5);
sql
SELECT sensor, day, value
FROM readings
FORMAT csv;
text
7,"2025-03-05",12.5

This key allows one reading per sensor and day. Declare explicit NOT NULL constraints where the row's identity must never be missing under ordinary table nullability rules.

See CREATE TABLE and CREATE INDEX.