복붙노트

[SQL] 포스트 그레스 : 목록 테이블 외래 키에 SQL

SQL

포스트 그레스 : 목록 테이블 외래 키에 SQL

주어진 테이블 목록에 모든 외래 키를 SQL을 사용하는 방법이 있나요? 나는 테이블 이름 / 스키마를 알고 그에 연결할 수 있습니다.

해결법

  1. ==============================

    1.당신은 INFORMATION_SCHEMA 테이블을 통해이 작업을 수행 할 수 있습니다. 예를 들면 :

    당신은 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';
  2. ==============================

    2.psql의이 작업을 수행하고, 당신이 psql 프로그램을 시작하는 경우 :

    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

    이 경우, 16,485 내가 찾고 있어요 테이블의 OID입니다 - 당신은 그냥 같은 regclass 형에 TABLENAME을 주조하여 하나를 얻을 수 있습니다 :

    WHERE r.conrelid = 'mytable'::regclass

    이 독특한 (또는 search_path의에서 첫 번째)이 아니라면 테이블 이름을 스키마 자격 :

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

    3.이 포스트 그레스 특정 아니므로 Ollyc의 대답은 좋다, 그러나, 때 외래 키 참조 하나 이상의 열을 분해. 다음 쿼리는 컬럼의 임의의 숫자 작동하지만 포스트 그레스 확장에 크게 의존 :

    이 포스트 그레스 특정 아니므로 Ollyc의 대답은 좋다, 그러나, 때 외래 키 참조 하나 이상의 열을 분해. 다음 쿼리는 컬럼의 임의의 숫자 작동하지만 포스트 그레스 확장에 크게 의존 :

    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
  4. ==============================

    4.는 인덱스와 외부 키를 보여주지 테이블 컬럼의 데이터 유형을 보여주는뿐만 아니라 프롬프트의 PostgreSQL에 문제 \ d를 + TABLENAME.

    는 인덱스와 외부 키를 보여주지 테이블 컬럼의 데이터 유형을 보여주는뿐만 아니라 프롬프트의 PostgreSQL에 문제 \ d를 + TABLENAME.

  5. ==============================

    5.ollyc 조리법에 확장 :

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

    그때:

    WHERE TABLE_NAME = 'YourTableNameHere'foreign_keys_view FROM SELECT *;

  6. ==============================

    6.솔루션에 대한 FF 게시물을 확인하고이 정보가 도움이 미세 때를 표시하는 것을 잊지 마세요

    솔루션에 대한 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');
  7. ==============================

    7.이 쿼리는 복합 키를 사용하여 올바른 작동 :

    이 쿼리는 복합 키를 사용하여 올바른 작동 :

    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
  8. ==============================

    8.난 당신과 아주 가까운 어떤 @ollyc 쓴 것은 이것이다 찾고 있던 무슨 생각 :

    난 당신과 아주 가까운 어떤 @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';

    이것은 외부 키로 지정된 테이블을 사용하는 모든 테이블을 나열합니다

  9. ==============================

    9.당신은 PostgreSQL의 시스템 카탈로그를 사용할 수 있습니다. 어쩌면 당신은 외래 키를 요청 pg_constraint을 조회 할 수 있습니다. 또한 정보 스키마를 사용할 수 있습니다

    당신은 PostgreSQL의 시스템 카탈로그를 사용할 수 있습니다. 어쩌면 당신은 외래 키를 요청 pg_constraint을 조회 할 수 있습니다. 또한 정보 스키마를 사용할 수 있습니다

  10. ==============================

    10.기존의 답변 중에 날 그래서 여기. 실제로 그들을 싶다고 형태로 결과 외래 키에 대한 정보를 찾는 내 (거대한) 쿼리입니다주지 않았다.

    기존의 답변 중에 날 그래서 여기. 실제로 그들을 싶다고 형태로 결과 외래 키에 대한 정보를 찾는 내 (거대한) 쿼리입니다주지 않았다.

    몇 가지 참고 사항 :

    -

    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;
  11. ==============================

    11.키가 참조하는에 기본 키의 이름을 사용하고 INFORMATION_SCHEMA를 조회 :

    키가 참조하는에 기본 키의 이름을 사용하고 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 '는 외래 키가 참조하는 기본 키의 이름입니다.

  12. ==============================

    12.다음은 PostgreSQL의 메일 링리스트에서 안드레아스 조셉 Krogh에 의해 해결책은 다음과 같습니다 http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

    다음은 PostgreSQL의 메일 링리스트에서 안드레아스 조셉 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;

    이 솔루션 핸들 외국의 여러 열을 참조하는 키 및 (다른 답변의 일부가해야 할 실패)을 피 중복. 변경된 유일한 것은 내가 변수 이름이었다.

    여기에 권한 테이블을 참조하는 모든 직원의 열을 반환하는 예제입니다 :

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

    13.여기 마틴의 훌륭한 대답에 확장하려면하면 외래 키 제약 조건의에 따라 종속 테이블 / 모든 열을 볼 수 있도록 부모 테이블과 쇼 당신은 각각의 부모 테이블과 자식 테이블의 이름을 기준으로 필터링 할 수있는 쿼리입니다 부모 테이블.

    여기 마틴의 훌륭한 대답에 확장하려면하면 외래 키 제약 조건의에 따라 종속 테이블 / 모든 열을 볼 수 있도록 부모 테이블과 쇼 당신은 각각의 부모 테이블과 자식 테이블의 이름을 기준으로 필터링 할 수있는 쿼리입니다 부모 테이블.

    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%'
  14. ==============================

    14.한 가지 다른 방법 :

    한 가지 다른 방법 :

    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
;
  15. ==============================

    15.짧지 만 달콤한

    짧지 만 달콤한

    select  * from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name='your_table_name' and position_in_unique_constraint notnull;
  16. ==============================

    16.

    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
  17. ==============================

    17.나는 그런 식으로 사용 자주 솔루션을 썼다. 코드는 http://code.google.com/p/pgutils/이다. pgutils.foreign_keys보기를 참조하십시오.

    나는 그런 식으로 사용 자주 솔루션을 썼다. 코드는 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와 함께 작동합니다. 앞으로 몇 개월, 필요하다면 나는 그것을 갱신 기대하고 있습니다.

    -Reece

  18. ==============================

    18.문제에 대한 적절한 해결책, INFORMATION_SCHEMA를 사용하여 다중 열 키 작업, sqlsever MS와 정확하고도 호환이 두 테이블에 다른 이름의 열을 결합 :

    문제에 대한 적절한 해결책, INFORMATION_SCHEMA를 사용하여 다중 열 키 작업, sqlsever MS와 정확하고도 호환이 두 테이블에 다른 이름의 열을 결합 :

    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 대신보기를 사용하기로 결정했다.

  19. ==============================

    19.나는 쿼리 한 후 비교 데이터베이스 스키마에 작은 도구를 만들었습니다 : 텍스트 PostgreSQL의 DB 스키마 덤프

    나는 쿼리 한 후 비교 데이터베이스 스키마에 작은 도구를 만들었습니다 : 텍스트 PostgreSQL의 DB 스키마 덤프

    이 FK에 대한 정보가 있지만 ollyc 반응은 더 자세한 정보를 제공합니다.

  20. ==============================

    20.참고 : 제약 열을 읽는 동안 음주 열의 순서를 잊지!

    참고 : 제약 열을 읽는 동안 음주 열의 순서를 잊지!

    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)
  21. ==============================

    21.이것은 내가 현재이 테이블을 나열합니다, 사용하고 무엇이며 fkey 제약 [삭제 테이블 절하고 현재 카탈로그에있는 모든 테이블을 나열합니다]입니다 :

    이것은 내가 현재이 테이블을 나열합니다, 사용하고 무엇이며 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')

    22. from https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 삭제하거나 부모 행을 업데이트 할 수 없습니다 : 외래 키 제약 조건이 실패  (0) 2020.03.19
[SQL] 빠른 게 뭔데, MySQL의에서 DISTINCT 또는 GROUP BY를 선택?  (0) 2020.03.19
[SQL] 다른 테이블 값으로 갱신 MySQL의 열에  (0) 2020.03.19
[SQL] 가입 및 UNION의 차이점은 무엇입니까?  (0) 2020.03.19
[SQL] 의 차이는 왼쪽 조인과 오른쪽 SQL 서버에 참여  (0) 2020.03.19
,

티스토리툴바