테이블 또는 열의 모든 외부 키를 표시하려면 어떻게 해야 합니까?

테이블 또는 열의 모든 외부 키를 표시하려면 어떻게 해야 합니까?

MySQL에서 특정 테이블을 가리키는 모든 외부 키 제약 조건 목록을 가져오려면 어떻게 해야 합니까?이는 이 Oracle 질문과 동일하지만 MySQL에 대한 질문입니다.

테이블의 경우:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

열의 경우:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

기본적으로 REFERENCED_를 변경했습니다.표_NAME과 참조_where 절의 COLUMN_NAME.

편집: 코멘트에서 지적한 바와 같이 이것은 OPs 질문에 대한 정답은 아니지만 이 명령어를 알아두면 유용합니다.이 질문은 제가 찾고 있던 것에 대한 구글에 나타났고, 다른 사람들이 찾을 수 있도록 이 답을 남겨두기로 결심했습니다.

SHOW CREATE TABLE `<yourtable>`;

MySQL: show crestraints on tables 명령어를 찾았습니다.

나는 단지 존재하는지 아닌 FK가 어떻게 기능하는지 보고 싶었기 때문에 이 방법이 필요했다.

InnoDB 및 정의된 FK를 사용하는 경우 information_schema 데이터베이스를 쿼리할 수 있습니다. 예:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

유용한 정보를 추가하기 위해 오래된 답변에 글을 올립니다.

저도 같은 문제가 있었습니다만, REFERENCED 테이블이나 컬럼명과 함께 RESTRAINED_TYPE도 보고 싶었습니다.그렇게,

1. 테이블 내의 모든 FK를 표시하려면:

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE()
   AND i.TABLE_NAME = '<yourtable>';
   

2. 스키마 내의 모든 테이블과 FK를 표시하려면 다음 절차를 수행합니다.

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE();
   

3. 데이터베이스 내의 모든 FK를 표시하려면:

   SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';
   

기억해!

InnoDB 스토리지 엔진을 사용하고 있습니다.외부 키를 추가한 후에도 표시되지 않는 것은 테이블에서 MyISAM을 사용하고 있기 때문일 수 있습니다.

확인하는 방법:

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

수정하려면 다음을 사용합니다.

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Node의 답변 대신 InnoDB와 정의된 FK를 사용하면 다음과 같은 information_schema 데이터베이스를 쿼리할 수 있습니다.

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND TABLE_NAME = '<table>'

<table>의 외부 키 또는

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND REFERENCED_TABLE_NAME = '<table>'

외부 키에 대해 <table>을 클릭합니다.

필요에 따라 UPDATE_RULE 및 DELETE_RULE을 얻을 수도 있습니다.

SQL의 제약 조건은 테이블의 데이터에 대해 정의된 규칙입니다.또한 제약조건은 테이블에 들어가는 데이터 유형을 제한합니다.새 데이터가 이러한 규칙을 준수하지 않으면 작업이 중단됩니다.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY';

은 '있다'를 사용해서 할 수 .select * from information_schema.table_constraints;

(테이블 데이터가 많이 생성됩니다).

당신은 또한 MySQL을:이 사용할 수 있다.

show create table tableName;

이 해결책은 오로지:어떤 경우(예를 들어 떨어지contraint)에 필요한 모든 관계는 물론 제약 조건은 이름 표시하지 않을 것이다.

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

특정 데이터베이스의 테이블을 체크하려면 쿼리 끝에 스키마 이름을 추가합니다.

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'database_name';

마찬가지로 특정 열 이름에 대해

및 table_name = 'table_name'

를 참조해 주세요.

이 투고에서 영감을 얻었다.

REFERCERED_ 사용방법TABLE_NAME은 항상 동작하는 것은 아니며 NULL 값일 수 있습니다.대신 다음 쿼리를 사용할 수 있습니다.

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';

FK(Foreign Key References)를 사용하여 FK(Foreign Key References)를 빠르게 나열하는 방법

KEY_COLUMN_USAGE view:

SELECT CONCAT( table_name, '.',
column_name, ' -> ',
referenced_table_name, '.',
referenced_column_name ) AS list_of_fks
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

이 쿼리는 제약조건과 모든 참조 테이블 및 참조 테이블이 동일한 스키마 내에 있다고 가정합니다.

자신의 코멘트를 추가합니다.

출처: 공식 mysql 매뉴얼.

또 다른 답변을 덧붙이는 것은 꺼림칙하지만, 원하는 것을 얻기 위해 다른 사람들에게 빌리고 빌리고 훔쳐야 했습니다.이것은 주어진 스키마의 테이블에서 FK를 포함한 모든 FK 관계의 완전한 목록입니다.두 가지 중요한 레코드 세트는 information_schema입니다.KEY_COLUMN_USAGE 및 information_schema.referential_constraints.원하는 속성이 없는 경우 KCU, RC의 코멘트를 해제하여 사용 가능한 속성을 확인합니다.

SELECT DISTINCT KCU.TABLE_NAME, KCU.COLUMN_NAME, REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, UPDATE_RULE, DELETE_RULE #, KCU.*, RC.*
FROM information_schema.KEY_COLUMN_USAGE KCU
INNER JOIN information_schema.referential_constraints RC ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE TABLE_SCHEMA = (your schema name)
AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY KCU.TABLE_NAME, KCU.COLUMN_NAME;

제가 생각해낸 해결책은 깨지기 쉬운 것입니다.외국어 키에 대한 django의 명명 규칙에 의존합니다.

USE information_schema;
tee mysql_output
SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'database_name';
notee

그리고 껍데기 안에서

grep 'refs_tablename_id' mysql_output

외부 키 열의 이름도 가져오는 경우:

SELECT i.TABLE_SCHEMA, i.TABLE_NAME, 
       i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, 
       k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
  FROM information_schema.TABLE_CONSTRAINTS i 
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k 
       ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
 WHERE i.TABLE_SCHEMA = '<TABLE_NAME>' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
 ORDER BY i.TABLE_NAME;

다른 답변에서는 제공되지 않는 업데이트 및 삭제 동작을 알아두면 도움이 되는 경우가 많습니다.자, 그럼 시작합니다.

SELECT cu.table_name,
       cu.column_name,
       cu.constraint_name,
       cu.referenced_table_name,
       cu.referenced_column_name,
       IF(rc.update_rule = 'NO ACTION', 'RESTRICT', rc.update_rule) AS update_rule,-- See: https://stackoverflow.com/a/1498015/2742117
       IF(rc.delete_rule = 'NO ACTION', 'RESTRICT', rc.delete_rule) AS delete_rule -- See: https://stackoverflow.com/a/1498015/2742117
FROM information_schema.key_column_usage cu
INNER JOIN information_schema.referential_constraints rc ON rc.constraint_schema = cu.table_schema
AND rc.table_name = cu.table_name
AND rc.constraint_name = cu.constraint_name
WHERE cu.referenced_table_schema = '<your schema>'
  AND cu.referenced_table_name = '<your table>';

다음과 같은 특정 외부 키를 포함하는 모든 테이블을 검색하려면employee_id

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('employee_id')
AND TABLE_SCHEMA='table_name';

(ORM에서 사용하기 위해) 테이블 간의 관계에 대한 조감도(vid-view)가 필요했습니다.이 페이지의 제안과 실험 후에 다음과 같은 질문을 정리했습니다.

SELECT
    KCU.CONSTRAINT_NAME,
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.REFERENCED_TABLE_NAME,
    KCU.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
        ON
                COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
            AND COLS.TABLE_NAME   = KCU.TABLE_NAME
            AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
        KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
    AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    KCU.TABLE_NAME,
    COLS.ORDINAL_POSITION

원하는 순서대로 필요한 것만 반환됩니다.

또, 집약 작성에 사용할 수 있도록 하기 위해서, 결과를 거의 처리하지 않습니다(사전의 형태로 변환).

"myprodb" MySql 데이터베이스가 있었고 이 데이터베이스의 모든 외부 키를 확인하기 위해 다음과 같은 간단한 명령을 사용했습니다.

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'myprodb' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

도움이 됐으면 좋겠다.

언급URL : https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column