Find the names of declarative base table constraints. Naming of constraints must be consistent. For instance, do not mix system-defined and user-defined names.
Type
General (Overview of some aspect of the database.)
SELECT DISTINCT
'base table' AS type,
constraint_column_usage.table_schema AS schema_name,
constraint_column_usage.table_name AS parent_name,
constraint_column_usage.constraint_name
FROM
information_schema.constraint_column_usage
WHERE table_schema 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
'base table' AS type,
n.nspname as table_schema,
c.relname as table_name,
o.conname as constraint_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace AS n on c.relnamespace=n.oid
inner join pg_authid AS a ON n.nspowner=a.oid
where o.contype in ('x')
and (n.nspname='public' OR a.rolname<>'postgres')
UNION SELECT
'domain' AS type,
constraint_schema,
domain_name,
constraint_name
FROM Information_schema.domain_constraints
WHERE constraint_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY constraint_name ASC;
Categories
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming
Queries of this category provide information about the style of naming.
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.