Contraintes de types incohérents

Un champ qui porte une clé étrangère vers un autre n’est pas obligé d’avoir le même type que le champ pointé. Si cela arrive, vous avez cependant probablement un problème de modélisation.

Pour les performances, ce peut être catastrophique : l’index sur la clé étrangère n’est pas forcément utilisable et les performances peuvent en souffrir. (Pour la détection des index manquants, voir Clefs étrangères).

SET search_path TO '_noschema'; -- force regclass to show all schemas
WITH ct AS
(  SELECT conname, conrelid, confrelid,
   unnest (conkey) AS conkey, unnest (confkey) AS confkey
   FROM pg_constraint
   WHERE contype = 'f'  -- Foreign key
)
SELECT
    conname AS constraint_name,
    conrelid::regclass AS referencer_table,
    confrelid::regclass AS referenced_table,
/*   ar.attnum AS referencer_keyn, */
   (ar.attname) AS referencer_key,
   /*tr.typname,*/
   pg_catalog.format_type(ar.atttypid, ar.atttypmod) AS typname1,
/*   af.attnum AS referenced_keyn, */
   (af.attname) AS referenced_key,
   /*tf.typname*/
    pg_catalog.format_type(af.atttypid, af.atttypmod) AS typname2
FROM
    ct
    INNER JOIN pg_class cr ON (cr.oid = ct.conrelid)
    INNER JOIN pg_class cf ON (cf.oid = ct.confrelid)
    INNER JOIN pg_attribute ar ON (ar.attnum = ct.conkey AND ar.attrelid = ct.conrelid)
    INNER JOIN pg_attribute af ON (af.attnum = ct.confkey AND af.attrelid = ct.confrelid)
    INNER JOIN pg_type tr ON (ar.atttypid = tr.oid)
    INNER JOIN pg_type tf ON (af.atttypid = tf.oid)
WHERE
    (cr.relkind IN ('r','p') AND cf.relkind IN ('r','p') )
    AND NOT ar.attisdropped AND NOT af.attisdropped
    AND ar.attnum > 0 AND af.attnum > 0
    AND (
     tr.typname != tf.typname
      OR
     pg_catalog.format_type(ar.atttypid, ar.atttypmod) != pg_catalog.format_type(af.atttypid, af.atttypmod)
     )
ORDER BY  referencer_table, constraint_name,
cf.relkind DESC, /* partitions after main table */
referenced_table;