특정 table.column을 참조하는 외부 키가 있고 외부 키에 대한 값이 있는 모든 테이블을 찾으려면 어떻게 해야 합니까?

특정 table.column을 참조하는 외부 키가 있고 외부 키에 대한 값이 있는 모든 테이블을 찾으려면 어떻게 해야 합니까?

다른 여러 테이블에서 기본 키가 외부 키로 참조되는 테이블이 있습니다.예를 들어 다음과 같습니다.

  CREATE TABLE `X` (
    `X_id` int NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY  (`X_id`)
  )
  CREATE TABLE `Y` (
    `Y_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Y_id`),
    CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )
  CREATE TABLE `Z` (
    `Z_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Z_id`),
    CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )

데이터베이스 내에 테이블 Y와 Z와 같이 X에 외부 키가 포함된 테이블이 몇 개 있는지 알 수 없습니다.반환에 사용할 수 있는 SQL 쿼리가 있습니까?

1. X에 외부 키가 있는 테이블 목록
2. 그리고 이들 테이블 중 실제로 외부 키에 값이 있는 테이블은 무엇입니까?

여기 있습니다.

USE information_schema;
SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id';

유사한 테이블/열 이름을 가진 데이터베이스가 여러 개 있는 경우, 쿼리를 특정 데이터베이스로 제한할 수도 있습니다.

SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id'
  AND TABLE_SCHEMA = 'your_database_name';

MySQL 5.5 레퍼런스 매뉴얼: "InnoDB 및 FORNE KEY 제약사항"

SELECT
  ke.REFERENCED_TABLE_SCHEMA parentSchema,
  ke.referenced_table_name parentTable,
  ke.REFERENCED_COLUMN_NAME parentColumnName,
  ke.TABLE_SCHEMA ChildSchema,
  ke.table_name childTable,
  ke.COLUMN_NAME ChildColumnName
FROM
  information_schema.KEY_COLUMN_USAGE ke
WHERE
  ke.referenced_table_name IS NOT NULL
  AND ke.REFERENCED_COLUMN_NAME = 'ci_id' ## Find Foreign Keys linked to this Primary Key
ORDER BY
  ke.referenced_table_name;

이 솔루션은 모든 관계를 표시할 뿐만 아니라 경우에 따라서는 필요한 제약 조건 이름도 표시합니다(예: 폐기 제약 조건).

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

모든 스키마 관련 정보를 현명한 이름으로 찾을 수 있습니다.information_schema테이블.

표를 확인해 보세요.REFERENTIAL_CONSTRAINTS그리고.KEY_COLUMN_USAGE. 전자는 다른 사람이 참조하는 테이블을 나타냅니다.후자는 각 필드가 어떻게 관련되어 있는지를 나타냅니다.

설명을 포함한 DB의 모든 외부 키 나열

    SELECT  
    i1.CONSTRAINT_NAME, i1.TABLE_NAME,i1.COLUMN_NAME,
    i1.REFERENCED_TABLE_SCHEMA,i1.REFERENCED_TABLE_NAME, i1.REFERENCED_COLUMN_NAME,
    i2.UPDATE_RULE, i2.DELETE_RULE 
    FROM   
    information_schema.KEY_COLUMN_USAGE AS i1  
    INNER JOIN 
    information_schema.REFERENTIAL_CONSTRAINTS AS i2 
    ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
    WHERE i1.REFERENCED_TABLE_NAME IS NOT NULL  
    AND  i1.TABLE_SCHEMA  ='db_name';

테이블 내의 특정 컬럼으로 제한

AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'

간단한 bash onliner를 작성했습니다.스크립트에 쓰면 알기 쉽게 출력할 수 있습니다.

mysql_references_to:

mysql -uUSER -pPASS -A DB_NAME -se "USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$1' AND REFERENCED_COLUMN_NAME = 'id'\G" | sed 's/^[ \t]*//;s/[ \t]*$//' |egrep "\<TABLE_NAME|\<COLUMN_NAME" |sed 's/TABLE_NAME: /./g' |sed 's/COLUMN_NAME: //g' | paste -sd "," -| tr '.' '\n' |sed 's/,$//' |sed 's/,/./'

실행은 다음과 같습니다.mysql_references_to transaccion(transaccion은 랜덤테이블명)은 다음과 같은 출력을 제공합니다.

carrito_transaccion.transaccion_id
comanda_detalle.transaccion_id
comanda_detalle_devolucion.transaccion_positiva_id
comanda_detalle_devolucion.transaccion_negativa_id
comanda_transaccion.transaccion_id
cuenta_operacion.transaccion_id
...

06 2022

@Panayotis 답변에 기반하지만 더 나은 구조를 가지고 있습니다.

그러면 여러 테이블의 모든 구속조건이 나열됩니다.
다음 항목을 포함했습니다.TABLE_SCHEMA데이터베이스 이름도 표시합니다.

SQL

SELECT
    TABLE_SCHEMA AS 'Database',
    TABLE_NAME AS t1,
    REFERENCED_TABLE_NAME AS 't2 (reference table)',
    COLUMN_NAME AS 't1 column',
    REFERENCED_COLUMN_NAME AS 't2 column (reference table)',
    CONSTRAINT_NAME AS 't1 (constrain name)'
FROM
    information_schema.key_column_usage
WHERE
    referenced_table_name IS NOT NULL

산출량

+------------+------------+---------------------+---------------+----------------------------+------------------------+
| Database   | t1         | t2 (reference table) | t1 column     | t2 column (reference table) | t1 (constrain name)    |
+============+============+=====================+===============+============================+========================+
| foobar     | credential | userdetail          | userdetail_fk | id                         | credentialUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| foobar     | loginlog   | userdetail          | userdetail_fk | id                         | loginlogUserdetailFk   |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
+ client     | userdetail | client              | client_fk     | id                         | userdetailClientFk     |
+------------+------------+---------------------+---------------+----------------------------+------------------------+

TABLE_NAME, COLUMN_NAME FROM information_schema를 선택합니다.키_컬럼_사용처CONSTRAINT_SCHEMA'your_database'처럼TABLE_SCHEMA'your_database'처럼REFERENCED_TABLE_SCHEMA'your_database'처럼REFERENCED_TABLE_NAME'테이블'처럼REFERENCED_COLUMN_NAME'your_column'처럼;

가장 간단:
1. phpMyAdmin을 엽니다.
2. 왼쪽에서 데이터베이스 이름을 클릭합니다.
3. 오른쪽 상단 모서리에서 "디자이너" 탭을 찾습니다.

모든 제약조건이 표시됩니다.

언급URL : https://stackoverflow.com/questions/806989/how-to-find-all-tables-that-have-foreign-keys-that-reference-particular-table-co