# List keys, constraints and indexes

Shape / inspect

A variant of [List columns and types](https://meridian.online/operations/list-columns-and-types).

List the keys, constraints, indexes, partitions, dependencies or relationships declared on tables.

Page: <https://meridian.online/operations/list-columns-and-types/list-keys-constraints-and-indexes>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

list the keys, constraints, indexes, partitions, dependencies and relationships declared on tables

```sql
CREATE TABLE dept (id INTEGER PRIMARY KEY, name VARCHAR UNIQUE);
CREATE TABLE emp (id INTEGER PRIMARY KEY, dept_id INTEGER REFERENCES dept (id), salary INTEGER CHECK (salary > 0), email VARCHAR NOT NULL);
CREATE INDEX emp_dept ON emp (dept_id);
CREATE SEQUENCE badge_numbers;
CREATE TABLE badges (badge INTEGER DEFAULT nextval('badge_numbers'));
-- partitions of a table kept as hive-style directories
COPY (SELECT i % 3 AS region, i FROM range(9) t(i)) TO '<your_scratch_dir>/inspect_parts' (FORMAT parquet, PARTITION_BY (region));
CREATE TEMP TABLE constraints AS
SELECT table_name, constraint_type, constraint_column_names AS columns, expression
FROM duckdb_constraints() WHERE table_name IN ('dept', 'emp');
CREATE TEMP TABLE keys AS SELECT table_name, constraint_type, columns FROM constraints WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE');
CREATE TEMP TABLE relationships AS
SELECT table_name AS child, constraint_column_names AS child_columns, referenced_table AS parent, referenced_column_names AS parent_columns
FROM duckdb_constraints() WHERE constraint_type = 'FOREIGN KEY';
CREATE TEMP TABLE indexes AS SELECT table_name, index_name, is_unique, expressions FROM duckdb_indexes();
CREATE TEMP TABLE partitions AS
SELECT DISTINCT regexp_extract(file, 'region=([^/]+)', 1) AS region FROM glob('<your_scratch_dir>/inspect_parts/**/*.parquet');
-- each dependency row names what is depended on and what depends on it
CREATE TEMP TABLE objects AS
SELECT table_oid AS oid, table_name AS name FROM duckdb_tables() UNION ALL
SELECT index_oid, index_name FROM duckdb_indexes() UNION ALL
SELECT sequence_oid, sequence_name FROM duckdb_sequences();
CREATE TEMP TABLE dependencies AS
SELECT a.name AS depended_on, b.name AS dependent
FROM duckdb_dependencies() d JOIN objects a ON a.oid = d.objid JOIN objects b ON b.oid = d.refobjid;
SELECT * FROM constraints ORDER BY ALL;
SELECT * FROM dependencies ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) FROM constraints) = 8 AND (SELECT list((table_name, constraint_type, columns) ORDER BY table_name, constraint_type) FROM keys) = [('dept', 'PRIMARY KEY', ['id']), ('dept', 'UNIQUE', ['name']), ('emp', 'PRIMARY KEY', ['id'])] AND (SELECT expression FROM constraints WHERE constraint_type = 'CHECK') = '(salary > 0)' AND (SELECT list(columns ORDER BY columns) FROM constraints WHERE constraint_type = 'NOT NULL' AND table_name = 'emp') = [['email'], ['id']] AND (SELECT list((child, child_columns, parent, parent_columns)) FROM relationships) = [('emp', ['dept_id'], 'dept', ['id'])] AND (SELECT list((table_name, index_name, is_unique, expressions)) FROM indexes) = [('emp', 'emp_dept', false, ['dept_id'])] AND (SELECT list(region ORDER BY region) FROM partitions) = ['0', '1', '2'] AND (SELECT list((depended_on, dependent) ORDER BY depended_on, dependent) FROM dependencies) = [('badge_numbers', 'badges'), ('dept', 'emp'), ('emp', 'emp_dept')]) THEN 'as expected' ELSE error('the declared keys, constraints, foreign key, index, partitions and dependencies were not the expected rows') END AS verdict;
```

Replace `<your_scratch_dir>` with a directory of your own that you can write to before running the statement.

The statement writes `inspect_parts` itself, in `<your_scratch_dir>`.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- DuckDB: `duckdb_constraints` (read 2026-09-27)
- DuckDB: `duckdb_dependencies` (read 2026-09-27)
- DuckDB: `duckdb_indexes` (read 2026-09-27)
- Power Query M: [`Table.Keys`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27)
- Power Query M: [`Table.PartitionKey`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27)
- Power Query M: [`Table.PartitionValues`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#information) (read 2026-09-27)
- Power Query M: [`Tables.GetRelationships`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#information) (read 2026-09-27)
- subtoken: [`pragma_hnsw_index_info`](https://duckdb.org/docs/current/core_extensions/vss.html) (read 2026-09-27)