SQL Query for a Primary Key: Inspect Composite-Key Order in SQLite

The practice task

Inspect the two-column primary key of a fictional attendance table. Return only key columns and preserve their key order instead of assuming that the first physical column is the entire key.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE attendance(day TEXT NOT NULL, person_id INTEGER NOT NULL, note TEXT, PRIMARY KEY(day,person_id));
INSERT INTO attendance VALUES ('2026-10-01',1,'morning'),('2026-10-01',2,'afternoon');

SELECT name, pk AS key_position FROM pragma_table_info('attendance')
WHERE pk > 0 ORDER BY pk;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The result identifies day as key position one and person_id as position two. The note field is absent because it is not part of the primary key. Both columns together define the attendance identity for this table.

A query that returns only one key column would provide an incomplete identity. The two fixture records share the same day but differ by person ID. Treating day alone as the key would incorrectly label them duplicates.

Other database products expose key metadata through their own catalogues or information-schema views. This query is deliberately SQLite-specific. Also inspect the declared schema when deciding whether every key component is required; the fixture states NOT NULL explicitly for both components.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Keep every component of a composite key.
• Preserve the key-position order reported by the catalogue.
• Use the schema-inspection interface for the actual database product.