Existe-t-il un moyen d’utiliser SQL pour répertorier toutes les clés étrangères d’une table donnée? Je connais le nom de la table / le schéma et je peux le twigr.
Vous pouvez le faire via les tables information_schema. Par exemple:
SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_schema AS foreign_table_schema, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
psql fait cela, et si vous lancez psql avec:
psql -E
il vous montrera exactement quelle requête est exécutée. Dans le cas de la recherche de clés étrangères, c’est:
SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1
Dans ce cas, 16485 est le nom de la table que je regarde – vous pouvez l’obtenir simplement en convertissant votre tablename en regclass comme:
WHERE r.conrelid = 'mytable'::regclass
Schéma-qualifier le nom de la table s’il n’est pas unique (ou le premier dans votre search_path
):
WHERE r.conrelid = 'myschema.mytable'::regclass
La réponse d’Ollyc est bonne car elle n’est pas spécifique à Postgres, mais elle se brise lorsque la clé étrangère fait référence à plusieurs colonnes. La requête suivante fonctionne pour un nombre arbitraire de colonnes, mais elle repose largement sur les extensions Postgres:
select att2.attname as "child_column", cl.relname as "parent_table", att.attname as "parent_column", conname from (select unnest(con1.conkey) as "parent", unnest(con1.confkey) as "child", con1.confrelid, con1.conrelid, con1.conname from pg_class cl join pg_namespace ns on cl.relnamespace = ns.oid join pg_constraint con1 on con1.conrelid = cl.oid where cl.relname = 'child_table' and ns.nspname = 'child_schema' and con1.contype = 'f' ) con join pg_atsortingbute att on att.attrelid = con.confrelid and att.attnum = con.child join pg_class cl on cl.oid = con.confrelid join pg_atsortingbute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent
Extension à la recette d’ollycémie:
CREATE VIEW foreign_keys_view AS SELECT tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY';
Alors:
SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere'
;
Issue \d+ tablename
à l’invite PostgreSQL, en plus de montrer les types de données de la colonne de table, elle affichera les index et les clés étrangères.
vérifiez le post ff pour votre solution et n’oubliez pas de le marquer quand cela vous convient
http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html
SELECT o.conname AS constraint_name, (SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema, m.relname AS source_table, (SELECT a.attname FROM pg_atsortingbute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column, (SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema, f.relname AS target_table, (SELECT a.attname FROM pg_atsortingbute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column FROM pg_constraint o LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid WHERE o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');
Je pense que ce que vous cherchiez et très proche de ce que @ollyc a écrit est le suivant:
SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='YourTableNameHere';
Cela listera toutes les tables qui utilisent votre table spécifiée comme clé étrangère
Cette requête fonctionne correctement avec les clés composites également:
select c.constraint_name , x.table_schema as schema_name , x.table_name , x.column_name , y.table_schema as foreign_schema_name , y.table_name as foreign_table_name , y.column_name as foreign_column_name from information_schema.referential_constraints c join information_schema.key_column_usage x on x.constraint_name = c.constraint_name join information_schema.key_column_usage y on y.ordinal_position = x.position_in_unique_constraint and y.constraint_name = c.unique_constraint_name order by c.constraint_name, x.ordinal_position
Vous pouvez utiliser les catalogues système PostgreSQL . Peut-être que vous pouvez interroger pg_constraint pour demander des clés étrangères. Vous pouvez également utiliser le schéma d’information
Voici une solution d’Andreas Joseph Krogh de la liste de diffusion PostgreSQL: http://www.postgresql.org/message-id/[email protected]
SELECT source_table::regclass, source_attr.attname AS source_column, target_table::regclass, target_attr.attname AS target_column FROM pg_atsortingbute target_attr, pg_atsortingbute source_attr, (SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints FROM (SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints, generate_series(1, array_upper(conkey, 1)) AS i FROM pg_constraint WHERE contype = 'f' ) query1 ) query2 WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;
Cette solution gère les clés étrangères qui font référence à plusieurs colonnes et évite les doublons (ce que certaines autres réponses ne permettent pas de faire). La seule chose que j’ai changée était les noms de variables.
Voici un exemple qui renvoie toutes employee
colonnes d’ employee
faisant référence à la table d’ permission
:
SELECT source_column FROM foreign_keys WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;
Pour développer l’excellente réponse de Martin, voici une requête qui vous permet de filtrer en fonction de la table parente et vous montre le nom de la table enfant avec chaque table parente afin que vous puissiez voir toutes les tables / colonnes dépendantes des contraintes de clé étrangères la table parent.
select con.constraint_name, att2.attname as "child_column", cl.relname as "parent_table", att.attname as "parent_column", con.child_table, con.child_schema from (select unnest(con1.conkey) as "parent", unnest(con1.confkey) as "child", con1.conname as constraint_name, con1.confrelid, con1.conrelid, cl.relname as child_table, ns.nspname as child_schema from pg_class cl join pg_namespace ns on cl.relnamespace = ns.oid join pg_constraint con1 on con1.conrelid = cl.oid where con1.contype = 'f' ) con join pg_atsortingbute att on att.attrelid = con.confrelid and att.attnum = con.child join pg_class cl on cl.oid = con.confrelid join pg_atsortingbute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent where cl.relname like '%parent_table%'
Utilisez le nom de la clé primaire à laquelle les clés font référence et interrogez le schéma information:
select table_name, column_name from information_schema.key_column_usage where constraint_name IN (select constraint_name from information_schema.referential_constraints where unique_constraint_name = 'TABLE_NAME_pkey')
Ici, “TABLE_NAME_pkey” est le nom de la clé primaire référencée par les clés étrangères.
Aucune des réponses existantes ne m’a donné des résultats dans la forme que je voulais en fait. Donc, voici ma requête (gargantuan) pour trouver des informations sur les clés étrangères.
Quelques notes:
from_cols
et to_cols
pourraient être grandement simplifiées avec Postgres 9.4 et WITH ORDINALITY
ultérieures en utilisant WITH ORDINALITY
plutôt que le hackery utilisant les fonctions de fenêtre que j’utilise. UNNEST
par UNNEST
. Je ne pense pas que ce sera le cas, mais je n’ai pas de clé étrangère à colonnes multiples dans mon dataset à tester. L’ajout des finitions 9.4 élimine complètement cette possibilité. ORDER BY
dans les fonctions d’agrégat) STRING_AGG
par ARRAY_AGG
si vous souhaitez un tableau de colonnes plutôt qu’une chaîne séparée par des virgules. –
SELECT c.conname AS constraint_name, (SELECT n.nspname FROM pg_namespace AS n WHERE n.oid=c.connamespace) AS constraint_schema, tf.name AS from_table, ( SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq) FROM ( SELECT ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq, attnum FROM UNNEST(c.conkey) AS t(attnum) ) AS t INNER JOIN pg_atsortingbute AS a ON a.attrelid=c.conrelid AND a.attnum=t.attnum ) AS from_cols, tt.name AS to_table, ( SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq) FROM ( SELECT ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq, attnum FROM UNNEST(c.confkey) AS t(attnum) ) AS t INNER JOIN pg_atsortingbute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum ) AS to_cols, CASE confupdtype WHEN 'r' THEN 'ressortingct' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_update, CASE confdeltype WHEN 'r' THEN 'ressortingct' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_delete, CASE confmatchtype::text WHEN 'f' THEN 'full' WHEN 'p' THEN 'partial' WHEN 'u' THEN 'simple' WHEN 's' THEN 'simple' ELSE NULL END AS match_type, -- In earlier postgres docs, simple was 'u'nspecified, but current versions use 's'imple. text cast is required. pg_catalog.pg_get_constraintdef(c.oid, true) as condef FROM pg_catalog.pg_constraint AS c INNER JOIN ( SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid ) AS tf ON tf.oid=c.conrelid INNER JOIN ( SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid ) AS tt ON tt.oid=c.confrelid WHERE c.contype = 'f' ORDER BY 1;
SELECT r.conname ,ct.table_name ,pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct WHERE r.contype = 'f' AND r.conname = ct.constraint_name ORDER BY 1
J’ai écrit une solution qui aime et utilise fréquemment. Le code est à l’ adresse http://code.google.com/p/pgutils/ . Voir la vue pgutils.foreign_keys.
Malheureusement, le résultat est trop verbeux pour être inclus ici. Cependant, vous pouvez l’essayer sur une version publique de la firebase database, comme ceci:
$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;
Cela fonctionne avec 8.3 au moins. Je prévois de le mettre à jour, si nécessaire, dans les prochains mois.
-Réece
Solution appropriée au problème, en utilisant information_schema
, en travaillant avec des clés multi-colonnes, en joignant correctement des colonnes de noms différents dans les deux tables et également compatible avec ms sqlsever:
select fks.TABLE_NAME as foreign_key_table_name , fks.CONSTRAINT_NAME as foreign_key_constraint_name , kcu_foreign.COLUMN_NAME as foreign_key_column_name , rc.UNIQUE_CONSTRAINT_NAME as primary_key_constraint_name , pks.TABLE_NAME as primary_key_table_name , kcu_primary.COLUMN_NAME as primary_key_column_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS fks -- foreign keys inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_foreign -- the columns of the above keys on fks.TABLE_CATALOG = kcu_foreign.TABLE_CATALOG and fks.TABLE_SCHEMA = kcu_foreign.TABLE_SCHEMA and fks.TABLE_NAME = kcu_foreign.TABLE_NAME and fks.CONSTRAINT_NAME = kcu_foreign.CONSTRAINT_NAME inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc -- referenced constraints on rc.CONSTRAINT_CATALOG = fks.CONSTRAINT_CATALOG and rc.CONSTRAINT_SCHEMA = fks.CONSTRAINT_SCHEMA and rc.CONSTRAINT_NAME = fks.CONSTRAINT_NAME inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks -- primary keys (referenced by fks) on rc.UNIQUE_CONSTRAINT_CATALOG = pks.CONSTRAINT_CATALOG and rc.UNIQUE_CONSTRAINT_SCHEMA = pks.CONSTRAINT_SCHEMA and rc.UNIQUE_CONSTRAINT_NAME = pks.CONSTRAINT_NAME inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_primary on pks.TABLE_CATALOG = kcu_primary.TABLE_CATALOG and pks.TABLE_SCHEMA = kcu_primary.TABLE_SCHEMA and pks.TABLE_NAME = kcu_primary.TABLE_NAME and pks.CONSTRAINT_NAME = kcu_primary.CONSTRAINT_NAME and kcu_foreign.ORDINAL_POSITION = kcu_primary.ORDINAL_POSITION -- this joins the columns where fks.TABLE_SCHEMA = 'dbo' -- replace with schema name and fks.TABLE_NAME = 'your_table_name' -- replace with table name and fks.CONSTRAINT_TYPE = 'FOREIGN KEY' and pks.CONSTRAINT_TYPE = 'PRIMARY KEY' order by fks.constraint_name, kcu_foreign.ORDINAL_POSITION
Remarque: Il existe certaines différences entre les implémentations de potgresql et de sqlserver de la fonction information_schema
qui donnent des résultats différents aux deux systèmes. Pour cette raison, j’ai décidé d’utiliser plutôt la vue KEY_COLUMN_USAGE.
J’ai créé un petit outil pour interroger puis comparer le schéma de la firebase database: Dump du schéma de firebase database PostgreSQL vers le texte
Il y a des informations sur FK, mais la réponse de l’ollycémie donne plus de détails.
Une autre façon:
WITH foreign_keys AS ( SELECT conname, conrelid, confrelid, unnest(conkey) AS conkey, unnest(confkey) AS confkey FROM pg_constraint WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass ) -- if confrelid, conname pair shows up more than once then it is multicolumn foreign key SELECT fk.conname as constraint_name, fk.confrelid::regclass as referenced_table, af.attname as pkcol, fk.conrelid::regclass as referencing_table, a.attname as fkcol FROM foreign_keys fk JOIN pg_atsortingbute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid JOIN pg_atsortingbute a ON a.attnum = conkey AND a.attrelid = fk.conrelid ORDER BY fk.confrelid, fk.conname ;
Remarque: N’oubliez pas l’ordre des colonnes lors de la lecture des colonnes de contraintes!
SELECT conname, attname FROM pg_catalog.pg_constraint c JOIN pg_catalog.pg_atsortingbute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE attrelid = 'schema.table_name'::regclass ORDER BY conname, array_position(c.conkey, a.attnum)
C’est ce que j’utilise actuellement, il listera une table et ses contraintes de fkey [remove clause clause et elle listera toutes les tables du catalogue courant]:
SELECT current_schema() AS "schema", current_catalog AS "database", "pg_constraint".conrelid::regclass::text AS "primary_table_name", "pg_constraint".confrelid::regclass::text AS "foreign_table_name", ( ssortingng_to_array( ( ssortingng_to_array( pg_get_constraintdef("pg_constraint".oid), '(' ) )[2], ')' ) )[1] AS "foreign_column_name", "pg_constraint".conindid::regclass::text AS "constraint_name", TRIM(( ssortingng_to_array( pg_get_constraintdef("pg_constraint".oid), '(' ) )[1]) AS "constraint_type", pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition" FROM pg_constraint AS "pg_constraint" JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace WHERE --fkey and pkey constraints "pg_constraint".contype IN ( 'f', 'p' ) AND "pg_namespace".nspname = current_schema() AND "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')