Есть ли способ с помощью SQL перечислить все внешние ключи для данной таблицы? Я знаю имя / схему таблицы и могу подключить ее.
Как вывести список внешних ключей таблицы
Ответы (24)
Вы можете сделать это через таблицы information_schema. Например:
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 tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu USING (constraint_schema, constraint_name) JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
- person EMP; 26.08.2011
psql делает это, и если вы запустите psql с:
psql -E
он покажет вам, какой именно запрос выполняется. В случае поиска внешних ключей это:
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
В данном случае 16485 - это oid таблицы, на которую я смотрю - вы можете получить его, просто приведя имя своей таблицы в regclass, например:
WHERE r.conrelid = 'mytable'::regclass
Укажите схему имени таблицы, если оно не уникальное (или первое в вашем search_path
):
WHERE r.conrelid = 'myschema.mytable'::regclass
SELECT conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.confrelid = 'myschema.mytable'::regclass;
- person regilero; 20.10.2015
psql -E -U username -d database ThenWHAT
?
- person Poutrathor; 17.01.2019
psql -E
указывает psql
отображать фактические запросы, стоящие за командами psql, например \dt
, \d
и т. Д. Так что это способ изучить, что делают эти команды. В ответе нет необходимости запускать фактический запрос.
- person user1071847; 14.01.2020
Вызов \d+ tablename
в приглашении PostgreSQL, помимо отображения типов данных столбца таблицы, он покажет индексы и внешние ключи.
Ответ Ollyc хорош, поскольку он не зависит от Postgres, однако он ломается, когда внешний ключ ссылается на более чем один столбец. Следующий запрос работает для произвольного количества столбцов, но он сильно зависит от расширений 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
'child_table'
на самом деле 'parent_table'
, потому что cl.relname
находится в таблице cl
, которая объединена с confrelid
, то есть таблицей, на которую имеется ссылка (родительской). Если вы хотите отфильтровать дочернюю таблицу, вы должны добавить join pg_class cl2 on cl2.oid = con.conrelid
, а затем использовать cl2.relname = 'child_table'
.
- person Teejay; 01.08.2017
Расширение рецепта 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';
Потом:
SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere'
;
проверьте сообщение ff для вашего решения и не забудьте отметить это, когда вы исправите это полезным
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');
Этот запрос также корректно работает с составными ключами:
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
короткий, но приятный голос за, если это сработает для вас.
select * from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name='your_table_name' and position_in_unique_constraint notnull;
Я думаю, что вы искали и очень близко к тому, что написал @ollyc, вот что:
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';
Это перечислит все таблицы, которые используют указанную вами таблицу в качестве внешнего ключа.
Еще один способ:
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
;
Ни один из существующих ответов не дал мне результатов в той форме, в которой я их действительно хотел. Итак, вот мой (гигантский) запрос на поиск информации о внешних ключах.
Несколько примечаний:
- Выражения, используемые для генерации
from_cols
иto_cols
, можно было бы значительно упростить в Postgres 9.4 и более поздних версиях, используяWITH ORDINALITY
, а не хакерство с использованием оконных функций, которое я использую. - Те же самые выражения полагаются на то, что планировщик запросов не изменяет порядок возврата результатов из
UNNEST
. Я не думаю, что это произойдет, но у меня нет внешних ключей с несколькими столбцами в моем наборе данных для тестирования. Добавление тонкостей 9.4 полностью исключает эту возможность. - Для самого запроса требуется Postgres 9.0 или новее (8.x не разрешал
ORDER BY
в агрегатных функциях) - Замените
STRING_AGG
наARRAY_AGG
, если вам нужен массив столбцов, а не строка, разделенная запятыми.
-
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;
Правильное решение проблемы, использование information_schema
, работа с ключами нескольких столбцов, правильное объединение столбцов с разными именами в обеих таблицах, а также совместимость с 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
Примечание. Между реализациями information_schema
potgresql и sqlserver есть некоторые различия, из-за которых верхний ответ дает разные результаты в двух системах - одна показывает имена столбцов для таблицы внешнего ключа, а другая - для таблицы первичного ключа. По этой причине я решил использовать вместо этого представление KEY_COLUMN_USAGE.
ORDINAL_POSITION
может дать неверный результат, если порядок столбцов во внешнем ключе отличается от порядка столбцов в ограничении уникальности. Я считаю, что вам следовало присоединиться к kcu_foreign.POSITION_IN_UNIQUE_CONSTRAINT = kcu_primary.ORDINAL_POSITION
Обновлению: Кроме того, внешний ключ может зависеть от ограничения UNIQUE, поэтому я думаю, вам следует удалить условие pks.CONSTRAINT_TYPE
и просто присоединиться к rc
к kcu_primary
напрямую
- person easd; 09.06.2020
Вы можете использовать системные каталоги PostgreSQL. Возможно, вы можете запросить pg_constraint, чтобы запросить внешние ключи . Вы также можете использовать Информационную схему
Используйте имя первичного ключа, на который ссылаются ключи, и запросите схему 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')
Здесь TABLE_NAME_pkey - это имя первичного ключа, на который ссылаются внешние ключи.
Вот решение Андреаса Джозефа Крога из списка рассылки 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;
Это решение обрабатывает внешние ключи, которые ссылаются на несколько столбцов, и избегает дубликатов (чего не делают некоторые другие ответы). Единственное, что я изменил, - это имена переменных.
Вот пример, который возвращает все employee
столбцы, которые ссылаются на permission
таблицу:
SELECT source_column
FROM foreign_keys
WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;
Чтобы расширить отличный ответ Мартина, вот запрос, который позволяет вам фильтровать на основе родительской таблицы и показывает вам имя дочерней таблицы с каждой родительской таблицей, чтобы вы могли видеть все зависимые таблицы / столбцы на основе ограничений внешнего ключа в родительская таблица.
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%'
Я создал небольшой инструмент для запроса и последующего сравнения схемы базы данных: Дамп схемы базы данных PostgreSQL в текст
Есть информация о FK, но ответ ollyc дает более подробную информацию.
Я написал решение, которое нравится и которым часто пользуюсь. Код находится по адресу http://code.google.com/p/pgutils/. См. Представление pgutils.foreign_keys.
К сожалению, вывод слишком многословен, чтобы включать его здесь. Однако вы можете попробовать его в общедоступной версии базы данных здесь, например:
$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;
Это работает по крайней мере с 8.3. Я планирую обновить его, если потребуется, в ближайшие несколько месяцев.
-Рис
Это то, что я сейчас использую, он перечислит таблицу и ее ограничения fkey [удалить предложение таблицы, и он перечислит все таблицы в текущем каталоге]:
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')
Примечание: не забывайте порядок столбцов при чтении столбцов ограничений!
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)
самый быстрый способ проверить прямо в ответе bash, полностью основанном на этом ответе
IFS='' read -r -d '' sql_code << EOF_SQL_CODE
SELECT
o.oid
, 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
, ROW_NUMBER () OVER (ORDER BY o.oid) as rowid
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 1=1
AND o.contype = 'f'
AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')
EOF_SQL_CODE
psql -d my_db -c "$sql_code"
Где $ 1 ('my_schema') - схема, а $ 2 ('my_table') - имя таблицы:
SELECT ss.conname constraint_name, a.attname column_name, ss.refnamespace fk_table_schema, ss.reflname fk_table_name, af.attname fk_column_name
FROM pg_attribute a, pg_attribute af,
(SELECT r.oid roid, c.conname, rf.relname reflname, information_schema._pg_expandarray(c.conkey) x,
nrf.nspname refnamespace, rf.oid rfoid, information_schema._pg_expandarray(cf.confkey) xf
FROM pg_namespace nr, pg_class r, pg_constraint c,
pg_namespace nrf, pg_class rf, pg_constraint cf
WHERE nr.oid = r.relnamespace
AND r.oid = c.conrelid
AND rf.oid = cf.confrelid
AND c.conname = cf.conname
AND nrf.oid = rf.relnamespace
AND nr.nspname = $1
AND r.relname = $2) ss
WHERE ss.roid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped
AND ss.rfoid = af.attrelid AND af.attnum = (ss.xf).x AND NOT af.attisdropped
ORDER BY ss.conname, a.attname;
Я обновил ответ @ollyc, который сейчас находится наверху. Я согласен с @fionbio, потому что key_column_usage
и constraint_column_usage
не имеют относительной информации на уровне столбца.
Если constraint_column_usage
имеет ordinal_positon
столбец, например key_column_usage
, его можно объединить с этим столбцом. Итак, я сделал от ordinal_position
до constraint_column_usage
, как показано ниже.
Я не могу подтвердить, что этот созданный вручную ordinal_position
находится в том же порядке, что и key_column_usage
. Но я проверил, что это точно такой же порядок, по крайней мере, в моем случае.
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 (select row_number() over (partition by table_schema, table_name, constraint_name order by row_num) ordinal_position,
table_schema, table_name, column_name, constraint_name
from (select row_number() over (order by 1) row_num, table_schema, table_name, column_name, constraint_name
from information_schema.constraint_column_usage
) t
) AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
AND ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'mytable'