I need to display the list of integrity constraints of my solution by indicating the name of the constraint, its type and the detail of the constraint (message that explains what validates the constraint), all sorted by table name and constraint name.
This is what I tried :
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'mytable'
How can I display all of that and also give a message that indicates what validates the constraint ?
This is a comment that doesn't fit the comments section.
I wanted to document that tables have 11+1 types of constraints:
PRIMARY KEY constraint.UNIQUE constraint.CHECK constraint.FOREIGN KEY constraint (aka reference).FOREIGN KEY constraint (aka referential action RESTRICT).NOT NULL constraint.PRIMARY KEY constraint.UNIQUE constraint.CHECK constraint.FOREIGN KEY constraint (aka reference).FOREIGN KEY constraint (aka referential action RESTRICT).WHERE to the CREATE UNIQUE INDEX ... statement. This type of constraints is, however, debated since the SQL Standard does not include it. In practice all major database engines (Oracle, DB2, PostgreSQL, etc.) implement them (albeit using different strategies) and they are very efficiently at maintaining data quality.Note: Some engines (e.g. Oracle) do not implement #7 to #11. For Oracle most constraints are recorded at the table level, even if you define them at the column level.
Please use below query (This query is tested on MS SQL, I hope you convert it for PostgreSQL):
Select self_objects.name 'Table_Name', C.*, (Select definition From sys.default_constraints Where object_id = C.object_id) As dk_definition,
(Select definition From sys.check_constraints Where object_id = C.object_id) As ck_definition,
(Select name From sys.objects Where object_id = D.referenced_object_id) As fk_table,
(Select name From sys.columns Where column_id = D.parent_column_id And object_id = D.parent_object_id) As fk_col
From sys.objects As C
join sys.objects self_objects on
self_objects.object_id = c.parent_object_id
and self_objects.type = 'U'
Left Join (Select * From sys.foreign_key_columns) As D On D.constraint_object_id = C.object_id
order by self_objects.name
Please try below code.
SELECT
"ns"."nspname" AS "table_schema",
"t"."relname" AS "table_name",
"cnst"."conname" AS "constraint_name", pg_get_constraintdef ( "cnst"."oid" ) AS "expression",
CASE
"cnst"."contype"
WHEN 'p' THEN
'PRIMARY'
WHEN 'u' THEN
'UNIQUE'
WHEN 'c' THEN
'CHECK'
WHEN 'x' THEN
'EXCLUDE'
END AS "constraint_type",
"a"."attname" AS "column_name"
FROM
"pg_constraint" "cnst"
INNER JOIN "pg_class" "t" ON "t"."oid" = "cnst"."conrelid"
INNER JOIN "pg_namespace" "ns" ON "ns"."oid" = "cnst"."connamespace"
LEFT JOIN "pg_attribute" "a" ON "a"."attrelid" = "cnst"."conrelid"
AND "a"."attnum" = ANY ( "cnst"."conkey" )