NOT VALID constraints and disabled triggers: the dormant guardrails
An inventory of CHECK/FK constraints created NOT VALID (never re-validated) and of disabled triggers: that many business rules the database no longer enforces.
Prerequisites
PostgreSQL (pg_constraint, pg_trigger)
SQL
SELECT
'constraint' AS type_objet,
con.conrelid::regclass::text AS table_name,
con.conname AS nom,
'NOT VALID — données existantes jamais vérifiées' AS probleme
FROM pg_constraint con
WHERE NOT con.convalidated
AND con.contype IN ('c', 'f')
UNION ALL
SELECT
'trigger',
tg.tgrelid::regclass::text,
tg.tgname,
'désactivé (tgenabled = D)'
FROM pg_trigger tg
WHERE tg.tgenabled = 'D'
AND NOT tg.tgisinternal
ORDER BY type_objet, table_name;Result
type_objet | table_name | nom | probleme ------------+-------------+--------------------------+------------------------------------------------- constraint | invoices | fk_invoices_customer | NOT VALID — données existantes jamais vérifiées constraint | order_lines | chk_qty_positive | NOT VALID — données existantes jamais vérifiées trigger | customers | trg_audit_customers | désactivé (tgenabled = D) trigger | stock | trg_stock_recalc | désactivé (tgenabled = D) (4 rows) Remède : ALTER TABLE ... VALIDATE CONSTRAINT / ALTER TABLE ... ENABLE TRIGGER
SQLAuditContraintesTriggers