Query goal: | Find the number of constraints in different schemas. The number of constraints in a database gives an indication about the state of enforcing constraints at the database level. |
Query type: | Sofware measure (Numeric values (software measures) about the database) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH constraints AS (select n.nspname as schema, c.relname, o.conname, CASE WHEN o.contype ='c' THEN 'TABLE CHECK' WHEN o.contype ='f' THEN 'FOREIGN KEY' WHEN o.contype ='p' THEN 'PRIMARY KEY' WHEN o.contype ='u' THEN 'UNIQUE' WHEN o.contype ='t' THEN 'CONSTRAINT TRIGGER' WHEN o.contype ='x' THEN 'EXCLUSION' END AS con_type from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_namespace n on n.oid=c.relnamespace where c.relkind = 'r' and n.nspname not in (select schema_name from information_schema.schemata where schema_name<>'public' and schema_owner='postgres' and schema_name is not null) union select n.nspname as schema, t.typname, o.conname, 'DOMAIN CHECK' AS con_type from pg_constraint o inner join pg_type t on t.oid = o.contypid inner join pg_namespace n on n.oid=t.typnamespace where o.contype ='c' and n.nspname not in (select schema_name from information_schema.schemata where schema_name<>'public' and schema_owner='postgres' and schema_name is not null)) SELECT schema, con_type, Count(*) AS number_of_constraints FROM constraints GROUP BY CUBE (schema, con_type) ORDER BY schema, con_type, Count(*) DESC; |
Collection name | Collection description |
---|---|
Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Category name | Category description |
---|---|
CHECK constraints | Queries of this category provide information about CHECK constraints. |
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |