반응형
Postgres : 테이블 외래 키를 나열하는 SQL
SQL을 사용하여 주어진 테이블에 대한 모든 외래 키를 나열하는 방법이 있습니까? 나는 테이블 이름 / 스키마를 알고 그것을 연결할 수 있습니다.
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';
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
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
문제 \d+ tablenamePostgreSQL의 프롬프트에는 테이블 컬럼의 데이터 유형을 보여주는뿐만 아니라 그것은 인덱스와 외부 키를 보여줄 것이다.
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';
이 쿼리는 복합 키에서도 올바르게 작동합니다.
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
솔루션의 ff post를 확인하고 도움이 될 때 이것을 표시하는 것을 잊지 마십시오.
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');
나는 당신이 찾고있는 것이 @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';
지정된 테이블을 외래 키로 사용하는 모든 테이블이 나열됩니다.
PostgreSQL 시스템 카탈로그를 사용할 수 있습니다 . pg_constraint 를 쿼리 하여 외래 키를 요청할 수 있습니다 . 정보 스키마를 사용할 수도 있습니다
기존 답변 중 어느 것도 실제로 결과를 원하는 형태로 결과를 얻지 못했습니다. 따라서 외래 키에 대한 정보를 찾는 (gargantuan) 쿼리는 다음과 같습니다.
몇 가지 참고 사항 :
- Postgres 9.4 이상에서 생성
from_cols하고 사용하는 표현to_cols은WITH ORDINALITY내가 사용하는 창 기능을 사용하는 해커가 아닌 Postgres 9.4 이상에서 크게 단순화 될 수 있습니다 . - 동일한 식은에서 반환 된 결과 순서를 변경하지 않고 쿼리 플래너에 의존합니다
UNNEST. 나는 그것을 생각하지 않지만 테스트 할 데이터 세트에 여러 열 외래 키가 없습니다. 9.4 Niceness를 추가하면이 가능성을 완전히 제거 할 수 있습니다. - 쿼리 자체에는 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를 쿼리하십시오.
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 메일 링리스트의 Andreas Joseph Krogh가 작성한 솔루션입니다. http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no
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;
Martin의 탁월한 답변을 확장하기 위해 부모 테이블을 기준으로 필터링하고 각 부모 테이블을 사용하여 자식 테이블의 이름을 표시하는 쿼리를 통해 외부 키 제약 조건에 따라 모든 종속 테이블 / 열을 볼 수 있습니다 부모 테이블.
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%'
짧지 만 달콤한
select * from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name='your_table_name' and position_in_unique_constraint notnull;
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
자주 좋아하는 솔루션을 작성했습니다. 코드는 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 이상에서 작동합니다. 필요한 경우 향후 몇 개월 내에 업데이트 할 것으로 예상됩니다.
-리시
다른 방법 :
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
;
Proper solution to the problem, using information_schema, working with multi column keys, joining columns of different names in both tables correctly and also compatible with 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
Note: There are some differences between potgresql and sqlserver implementations of information_schema which make the top answer give different results on the two systems - one shows column names for the foreign key table the other for the primary key table. For this reason I decided to use KEY_COLUMN_USAGE view instead.
I created little tool to query and then compare database schema: Dump PostgreSQL db schema to text
There is info about FK, but ollyc response gives more details.
Note: Do not forget column's order while reading constraint columns!
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)
This is what I'm currently using, it will list a table and it's fkey constraints [remove table clause and it will list all tables in current catalog]:
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')
참고URL : https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys
반응형
'Programming' 카테고리의 다른 글
| CSS에서 PNG 이미지의 그림자 (0) | 2020.05.17 |
|---|---|
| 입력 번호에서 스피너 숨기기-Firefox 29 (0) | 2020.05.17 |
| 유닉스 쉘에서 숫자 열을 더하십시오 (0) | 2020.05.17 |
| 숫자 0..9를 2 자리로 표시하는 방법 (날짜가 아님) (0) | 2020.05.17 |
| C ++에서 함수 내부에 함수를 가질 수 있습니까? (0) | 2020.05.17 |