Jak wyświetlić wszystkie klucze obce do tabeli lub kolumny?

W MySQL, w jaki sposób uzyskać listę wszystkich ograniczeń kluczy obcych wskazujących na określoną tabelę? konkretna kolumna? To jest to samo, co pytanie Oracle , ale w przypadku MySQL.

Do stołu:

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

Dla kolumny:

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

Zasadniczo zmieniliśmy REFERENCED_TABLE_NAME na REFERENCED_COLUMN_NAME w klauzuli where.

EDYCJA: Jak wskazano w komentarzach, nie jest to poprawna odpowiedź na pytanie PO, ale warto znać to polecenie. To pytanie pojawiło się w Google, czego szukałem, i pomyślałem, że zostawię tę odpowiedź innym.

SHOW CREATE TABLE `<yourtable>`;

Znalazłem tę odpowiedź tutaj: MySQL: pokaż ograniczenia komendy tabel

Potrzebowałem tego, ponieważ chciałem zobaczyć, jak działa FK, a nie tylko sprawdzić, czy istnieje.

Jeśli używasz InnoDB i zdefiniowałeś FK, możesz zapytać o bazę danych information_schema, np .:

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

Publikowanie starej odpowiedzi w celu dodania użytecznych informacji.

Miałem podobny problem, ale chciałem też zobaczyć CONSTRAINT_TYPE wraz z tabelą REFERENCED i nazwami kolumn. Więc,

1. Aby zobaczyć wszystkie FK w tabeli:

   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. Aby zobaczyć wszystkie tabele i FK w schemacie:

   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. Aby zobaczyć wszystkie FK w bazie danych:

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

Zapamiętaj!

Wykorzystuje to silnik pamięci InnoDB. Jeśli po dodaniu ich nie możesz wyświetlić żadnych obcych kluczy, prawdopodobnie oznacza to, że Twoje tabele używają MyISAM.

Sprawdzić:

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

Aby to naprawić, użyj tego:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Alternatywnie do odpowiedzi Węzła, jeśli używasz InnoDB i zdefiniowałeś FK, możesz zapytać do bazy danych schemat_informacyjny np .:

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

dla kluczy obcych z <tabela> lub

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

dla kluczy obcych do <tabela>

Możesz także uzyskać UPDATE_RULE i DELETE_RULE, jeśli chcesz.

To rozwiązanie wyświetli nie tylko wszystkie relacje, ale także nazwę ograniczenia, która jest wymagana w niektórych przypadkach (np. Ograniczenie upuszczenia):

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;

Jeśli chcesz sprawdzić tabele w określonej bazie danych, na końcu zapytania dodaj nazwę schematu:

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

Podobnie, dla konkretnej nazwy kolumny dodaj

i nazwa_tabeli = 'nazwa_tabeli

na końcu zapytania.

Inspirowany tym postem tutaj

Korzystanie z REFERENCED_TABLE_NAME nie zawsze działa i może mieć wartość NULL. Zamiast tego może działać następujące zapytanie:

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

Szybki sposób na wyświetlenie listy FK (odniesienia do klucza obcego) za pomocą

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;

W tym zapytaniu założono, że ograniczenia oraz wszystkie tabele, do których istnieją odniesienia i tabele, są w tym samym schemacie.

Dodaj swój komentarz.

Źródło: oficjalny podręcznik mysql.

Rozwiązanie, które wymyśliłem, jest kruche; opiera się na konwencji nazewnictwa django dla kluczy obcych.

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

Następnie w skorupce

grep 'refs_tablename_id' mysql_output

Aby znaleźć wszystkie tabele zawierające określony klucz obcy, taki jakemployee_id

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

Jeśli chcesz również uzyskać nazwę kolumny klucza obcego:

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;