Postgres: SQL per elencare le chiavi esterne della tabella

C’è un modo con SQL per elencare tutte le chiavi esterne per una data tabella? Conosco il nome / schema della tabella e posso collegarlo.

Puoi farlo tramite le tabelle information_schema. Per esempio:

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 fa questo, e se si avvia psql con:

 psql -E 

ti mostrerà esattamente quale query viene eseguita. Nel caso di trovare chiavi esterne, è:

 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 

In questo caso, 16485 è l’oid del tavolo che sto guardando – puoi ottenerlo semplicemente lanciando il tuo tablename a regclass come:

 WHERE r.conrelid = 'mytable'::regclass 

Schema: qualifica il nome della tabella se non è univoco (o il primo nel tuo search_path ):

 WHERE r.conrelid = 'myschema.mytable'::regclass 

La risposta di Ollyc è buona in quanto non specifica per Postgres, tuttavia, si rompe quando la chiave esterna fa riferimento a più di una colonna. La seguente query funziona per un numero arbitrario di colonne, ma fa molto affidamento sulle estensioni 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_attribute att on att.attrelid = con.confrelid and att.attnum = con.child join pg_class cl on cl.oid = con.confrelid join pg_attribute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent 

Estensione alla ricetta ollyc:

 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'; 

Poi:

SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere' ;

Problema \d+ tablename sul prompt PostgreSQL, oltre a mostrare i tipi di dati della colonna della tabella mostrerà gli indici e le chiavi esterne.

controlla la posta elettronica per la tua soluzione e non dimenticarti di segnare questa cosa quando stai bene

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_attribute 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_attribute 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'); 

Penso che quello che stavi cercando e molto vicino a ciò che @ollyc ha scritto è questo:

 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'; 

Questo elencherà tutte le tabelle che utilizzano la tabella specificata come chiave esterna

Questa query funziona correttamente anche con i tasti compositi:

 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 

È ansible utilizzare i cataloghi di sistema PostgreSQL . Forse puoi interrogare pg_constraint per chiedere le chiavi esterne. È inoltre ansible utilizzare lo schema di informazioni

Ecco una soluzione di Andreas Joseph Krogh dalla mailing list 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_attribute target_attr, pg_attribute 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; 

Questa soluzione gestisce chiavi esterne che fanno riferimento a più colonne ed evita duplicati (che alcune delle altre risposte non riescono a fare). L’unica cosa che ho cambiato erano i nomi delle variabili.

Ecco un esempio che restituisce tutte le colonne dei employee che fanno riferimento alla tabella delle permission :

 SELECT source_column FROM foreign_keys WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass; 

Per approfondire l’eccellente risposta di Martin ecco una query che ti consente di filtrare in base alla tabella padre e mostra il nome della tabella figlio con ogni tabella genitore in modo da poter vedere tutte le tabelle / colonne dipendenti in base ai vincoli di chiave esterna in la tabella genitore.

 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_attribute att on att.attrelid = con.confrelid and att.attnum = con.child join pg_class cl on cl.oid = con.confrelid join pg_attribute att2 on att2.attrelid = con.conrelid and att2.attnum = con.parent where cl.relname like '%parent_table%' 

Utilizza il nome della chiave primaria a cui fanno riferimento le chiavi e invia una query a information_schema:

 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') 

Qui ‘TABLE_NAME_pkey’ è il nome della chiave primaria a cui fanno riferimento le chiavi esterne.

Nessuna delle risposte esistenti mi ha dato risultati nella forma in cui effettivamente le volevo. Quindi ecco la mia (gigantesca) query per trovare informazioni sulle chiavi esterne.

Alcune note:

  • Le espressioni utilizzate per generare from_cols e to_cols potrebbero essere notevolmente semplificate su Postgres 9.4 e successive utilizzando WITH ORDINALITY piuttosto che usare l’hackery che utilizza la funzione window.
  • Queste stesse espressioni si basano sul pianificatore di query che non altera l’ordine dei risultati UNNEST da UNNEST . Non penso che lo farà, ma non ho alcuna chiave esterna a più colonne nel mio set di dati per testare con. Aggiungere le sottigliezze 9.4 elimina completamente questa possibilità.
  • La query stessa richiede Postgres 9.0 o successivo (8.x non ha consentito ORDER BY in funzioni aggregate)
  • Sostituisci STRING_AGG con ARRAY_AGG se desideri un array di colonne anziché una stringa separata da virgole.

 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_attribute 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_attribute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum ) AS to_cols, CASE confupdtype WHEN 'r' THEN 'restrict' 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 'restrict' 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 

Ho scritto una soluzione che mi piace e uso frequentemente. Il codice è all’indirizzo http://code.google.com/p/pgutils/ . Vedi la vista pgutils.foreign_keys.

Sfortunatamente, l’output è troppo prolisso per includerlo qui. Tuttavia, puoi provarlo su una versione pubblica del database qui, in questo modo:

 $ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys; 

Funziona con almeno 8.3. Prevedo di aggiornarlo, se necessario, nei prossimi mesi.

-Reece

Soluzione corretta del problema, utilizzando information_schema , lavorando con chiavi a più colonne, unendo le colonne di nomi diversi in entrambe le tabelle in modo corretto e compatibile anche con 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 

Nota: Esistono alcune differenze tra le implementazioni di potgresql e sqlserver di information_schema che rendono la risposta in alto dare risultati diversi sui due sistemi: uno mostra i nomi delle colonne per la tabella delle chiavi esterne e l’altro per la tabella delle chiavi primaria. Per questo motivo ho deciso di utilizzare invece la vista KEY_COLUMN_USAGE.

Ho creato un piccolo strumento per interrogare e quindi confrontare lo schema del database: eseguire il dump dello schema db PostgreSQL in testo

Ci sono informazioni su FK, ma la risposta ollyc fornisce maggiori dettagli.

Un altro modo:

 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_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid ORDER BY fk.confrelid, fk.conname ; 

Nota: non dimenticare l’ordine della colonna durante la lettura delle colonne dei vincoli!

 SELECT conname, attname FROM pg_catalog.pg_constraint c JOIN pg_catalog.pg_attribute 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) 

Questo è quello che sto attualmente usando, verrà elencata una tabella e sono i vincoli di fkey [rimuovi la clausola della tabella e elencherà tutte le tabelle nel catalogo corrente]:

 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", ( string_to_array( ( string_to_array( pg_get_constraintdef("pg_constraint".oid), '(' ) )[2], ')' ) )[1] AS "foreign_column_name", "pg_constraint".conindid::regclass::text AS "constraint_name", TRIM(( string_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')