Jak wyświetlić wszystkie klucze obce w tabeli lub kolumnie?

EDIT: jak zaznaczono w komentarzach, nie jest to poprawna odpowiedź na pytanie OPs, ale warto znać tę komendę. To pytanie pojawiło się w Google za to, czego szukałem, i pomyślałem, że zostawię tę odpowiedź dla innych, aby znaleźć.

SHOW CREATE TABLE `<yourtable>`;

Znalazłem tę odpowiedź tutaj: MySQL: show constraints on tables command

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

Jeśli korzystasz z InnoDB i zdefiniowanych FK, możesz odpytywać bazę 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';

Zamieszczanie starej odpowiedzi, aby dodać kilka przydatnych informacji.

Miałem podobny problem, ale chciałem również zobaczyć CONSTRAINT_TYPE wraz z nazwami tabeli i kolumn. Więc

1. Aby zobaczyć wszystkie FKs 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 FKs 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 FKs 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';
   

Pamiętaj!

To jest korzystanie z magazynu InnoDB silnik. Jeśli nie możesz uzyskać żadnych kluczy obcych, aby pokazać się po ich dodaniu, prawdopodobnie dlatego, że Twoje tabele używają MyISAM.

Do sprawdzenia:

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

Aby naprawić, użyj tego:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Jako alternatywę dla odpowiedzi Node, jeśli używasz InnoDB i zdefiniowanych FK, możesz odpytywać bazę information_schema 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

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

Możesz również 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. Drop 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;

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

And table_name = ' table_name

Na końcu zapytania.

Zainspirowany tym postem tutaj

Używanie REFERENCED_TABLE_NAME nie zawsze działa i może być wartością 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 FKS (odniesień do kluczy obcych) 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;

To zapytanie zakłada, że ograniczenia oraz wszystkie tabele odniesienia i odniesienia znajdują się w tym samym schemacie.

Dodaj własny 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 skorupie,

grep 'refs_tablename_id' mysql_output

Niechętnie dodaję jeszcze jedną odpowiedź, ale musiałem błagać, pożyczać i kraść od innych, aby uzyskać to, czego chcę, czyli pełną listę wszystkich relacji FK na tabelach w danym schemacie, w tym FKs do tabel w innych schematach. Dwa kluczowe zestawy rekordów to information_schema.KEY_COLUMN_USAGE i information_schema.referential_constraints. Jeśli brakuje atrybutu, który chcesz, po prostu odkomentuj KCU., RC. aby zobaczyć, co jest dostępne

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;

Aby znaleźć wszystkie tabele zawierające konkretny klucz obcy , takie jak employee_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;

Ograniczenia w SQL są regułami zdefiniowanymi dla danych w tabeli. Ograniczenia ograniczają również typy danych, które trafiają do tabeli. Jeśli nowe dane nie będą przestrzegać tych zasad, akcja zostaje przerwana.

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

Możesz wyświetlić wszystkie ograniczenia używając select * from information_schema.table_constraints;

(spowoduje to wygenerowanie dużej ilości danych w tabeli).

Możesz również użyć tego dla MySQL:

show create table tableName;