List keys, constraints and indexes

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;