A primary key identifies every row uniquely. It can be one column or a combination of columns, and it protects table integrity by rejecting duplicate keys. section 3.2.2 section 3.4.1
Choose the key shape that matches how the data is identified:
- Use a single natural key when an existing attribute is reliably unique. section 3.2.2
- Use a composite primary key when uniqueness depends on multiple values, such as a system identifier and reading timestamp. section 3.3.1
- Use a surrogate key when source attributes are not unique; generated integers or UUIDs can provide stable row identifiers. section 3.2.2
- Keep repeated observations in the referencing table: a foreign key may repeat even though the referenced primary key cannot. section 3.4.4
Define single, composite, and generated keys
CREATE TABLE systems ( id INTEGER PRIMARY KEY, name VARCHAR(128) NOT NULL ); CREATE TABLE readings ( system_id INTEGER NOT NULL, read_on TIMESTAMP NOT NULL, power DECIMAL(10,3) NOT NULL DEFAULT 0 CHECK (power >= 0), PRIMARY KEY (system_id, read_on), FOREIGN KEY (system_id) REFERENCES systems(id) ); CREATE SEQUENCE prices_id INCREMENT BY 1 MINVALUE 10; CREATE TABLE prices ( id INTEGER PRIMARY KEY DEFAULT(nextval('prices_id')), value DECIMAL(5,2) NOT NULL, valid_from DATE NOT NULL, CONSTRAINT prices_uk UNIQUE (valid_from) ); section 3.3.1
Insertion must respect primary-key uniqueness. Supplying an explicit identifier twice raises a constraint error; omitting a sequence-backed identifier lets its default generate a new value. ON CONFLICT can suppress the error or update a row when a different unique constraint represents the business rule. section 3.4.1
Figure 3.1 Energy consumption schema
Inspect the energy-consumption schema to see systems, readings, and prices connected through primary and foreign keys. section 3.2.2
