Cum văd toate cheile externe într-o masă sau într-o coloană?

În MySQL, cum obțin o listă a tuturor constrângerilor cheie străine care indică spre o anumită masă? o anumită coloană? Acesta este același lucru cu această întrebare Oracle , dar pentru MySQL.

413

11 răspunsuri

Pentru o masă:

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

For a Column:

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

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

569
adăugat
@Acute: Sunteți sigur că întrebați despre masa corectă? Daca interogarea Node functioneaza, atunci probabil va intrebati despre cealalta directie (de exemplu cheile FROM mytable, nu cheile TO mytable.) Aceasta asteapta ca ai scris '<table>' cu numele tabelului si fara '<' si ' ?
adăugat autor Vinko Vrsalovic, sursa
De ce este nevoie atât de mult? De ce să nu faceți așa de ușor cum selectați rândurile?
adăugat autor Lealo, sursa
În mod obișnuit, am doar un set gol aici! Răspunsul lui Andy a lucrat cel mai bine pentru mine.
adăugat autor omarjebari, sursa
@ DeepakRam Aceasta funcționează perfect pentru mine cu un utilizator privilegiat care nu este utilizatorul rădăcină.
adăugat autor Matthew Read, sursa
Asta a fost perfect pentru mine. Am cele mai multe privilegii, dar nu și rădăcini. Ești un salvator de viață.
adăugat autor jDub9, sursa
acest lucru îmi dă întotdeauna un set gol, în timp ce interogarea propusă de nodul de mai jos funcționează bine
adăugat autor Acute, sursa
Se pare că am înțeles greșit interogarea, pentru că am interogat cheile de referință de la <table> :) (da, am scris numele tabelului în loc de "<table>" XD)
adăugat autor Acute, sursa
Cu excepția cazului în care sunteți sigur că numele dvs. de tabel este unic, probabil că doriți să restricționați interogarea dvs. și la o anumită bază de date. Schimbați clauza de unde: unde REFERENCED_TABLE_SCHEMA = 'mydatabase' și REFERENCED_TABLE_NAME = 'mytable'
adăugat autor user12345, sursa
Acest lucru nu va funcționa în caz de utilizator non root, chiar dacă utilizatorul are toate permisiunile pentru baza de date
adăugat autor Deepak Ram, sursa

EDIT: Așa cum sa subliniat în observații, acesta nu este răspunsul corect la întrebarea OP, dar este util să cunoașteți această comandă. Această întrebare a apărut în Google pentru ceea ce căutam și mi-am imaginat că voi lăsa acest răspuns pentru ca ceilalți să-l găsească.

SHOW CREATE TABLE ``;

I found this answer here: MySQL : show constraints on tables command

Aveam nevoie de acest lucru pentru că am vrut să văd cum FK funcționa, mai degrabă decât să văd dacă există sau nu.

194
adăugat
După cum afirmă @Barmar, acest lucru este în întregime greșit; acesta va afișa cheile străine aparținând tabelului specificat, dar nu va afișa cheile străine care să indice tabelul TO , ceea ce întreabă întrebarea. Nici o idee despre modul în care au primit 50 de voturi; Cred că oamenii au ajuns aici, când au căutat într-adevăr răspunsul la întrebarea opusă, au găsit răspunsul lor aici oricum și nu s-au deranjat să citească întrebarea inițială (sau chiar titlul său) înainte de a fi invitați.
adăugat autor Mark Amery, sursa
Aceasta arată toate constrângerile din , nu toate constrângerile care indică .
adăugat autor Barmar, sursa
thisi arata de fapt toate constrangerile care exista, phpmyadmin va arata doar cel care arata spre masa ta. pare suficient de bun pentru a evita duplicarea cu un instrument ORM
adăugat autor william.eyidi, sursa
@MarkAmery: acesta este primul rezultat al afișării cheilor externe MySQL în google, poate că este motivul pentru care;)
adăugat autor Jigar, sursa
Aceasta este ceea ce căutam, așa că mulțumesc că l-ați postat, chiar dacă nu a răspuns la întrebarea OP. Nu doare niciodată să știi cum să te uiți în ambele direcții într-o relație!
adăugat autor Charles Wood, sursa

Dacă utilizați InnoDB și FK definit, puteți interoga baza de date information_schema, de ex .:

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';
58
adăugat
de fapt, aceasta indică direcția greșită. acea interogare arată toate cheile străine care indică FROM 'mytable', nu toate cheile străine indicând TO 'mytable'.
adăugat autor Christian Oudard, sursa
Puteți obține chei străine în ambele direcții din tabela REFERENTIAL_CONSTRAINTS - am adăugat un alt răspuns cu interogarea.
adăugat autor ChrisV, sursa
Acest lucru funcționează mai bine în cazul meu. Trebuie să renunț la orice constrângere de chei străine (și numai pe acelea) dintr-o masă pentru a putea schimba motorul InnoDB MyISAM sau NDB.
adăugat autor Kohányi Róbert, sursa

Postarea unui răspuns vechi pentru a adăuga câteva informații utile.

Am avut o problemă similară, dar am vrut, de asemenea, să văd CONSTRAINT_TYPE împreună cu numele tabelului și coloanelor REFERENCED. Asa de,

  1. To see all FKs in your table:

    USE '';
    
    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 = '';
    
  2. To see all the tables and FKs in your schema:

    USE '';
    
    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. To see all the FKs in your database:

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

Nu uitați!

Aceasta este folosirea motorului de stocare InnoDB. Dacă nu puteți obține chei străine care să apară după adăugarea lor, probabil că tabelele dvs. utilizează MyISAM.

A verifica:

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

Pentru a repara, utilizați acest lucru:

ALTER TABLE `` ENGINE=InnoDB;
32
adăugat
MyISAM nu suportă chei externe, din păcate. dev.mysql.com/doc/refman/5.7/ en/MyISAM-storage-engine.html
adăugat autor Andy, sursa
răspuns rațional. Aveți soluții pentru MyISAM?
adăugat autor Beau Bouchard, sursa
Aceste interogări rulează mult mai repede (de la 2 secunde până la 0,0015 secunde) dacă specificați k.TABLE_SCHEMA = DATABASE() și k.TABLE_NAME = '<table>' în clauza WHERE, așa cum este documentat aici dev.mysql.com/doc/refman/5.5/en/…
adăugat autor guigouz, sursa

Ca alternativă la răspunsul Node, dacă utilizați InnoDB și FK definit, puteți interoga baza de date information_schema, de exemplu:

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

for foreign keys from <table>, or

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

for foreign keys to <table>

You can also get the UPDATE_RULE and DELETE_RULE if you want them.

17
adăugat
Eu personal prefer acest răspuns deoarece folosirea tabelului REFERENTIAL_CONSTRAINTS vă oferă regula de actualizare și cascadă. +1
adăugat autor Luke Madhanga, sursa
Făcând descoperiri despre o masă, nu trebuie să uiți că cheile străine pot fi stabilite atât de căi!
adăugat autor Encoder, sursa

Această soluție nu numai că va afișa toate relațiile, ci și numele de constrângere, care este necesar în unele cazuri (de exemplu, 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;

Dacă doriți să verificați tabele într-o bază de date specifică, la sfârșitul interogării, adăugați numele tabelului:

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

De asemenea, pentru un anumit nume de coloană, adăugați

și table_name = 'nume_tabel

la sfârșitul interogării.

Inspired by this post here

7
adăugat

O modalitate rapidă de a vă lista FK-urile (referințele de la Foreign Key) folosind

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;

Această interogare presupune că constrângerile și toate tabelele de referință și de referință sunt în aceeași schemă.

Adăugați propriul comentariu.

Sursa: manualul oficial mysql.

2
adăugat

To find all tables containing a particular foreign key such as employee_id

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

Soluția cu care am venit a fost fragilă; se bazează pe convenția de numire a django pentru cheile străine.

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

Apoi, în cochilie,

grep 'refs_tablename_id' mysql_output
1
adăugat

Utilizarea REFERENCED_TABLE_NAME nu funcționează întotdeauna și poate fi o valoare NULL. Următorul interogare poate funcționa:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';
1
adăugat

Dacă doriți, de asemenea, să obțineți numele coloanei de chei străine:

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;
0
adăugat
MySQL - comunitatea Română
MySQL - comunitatea Română
12 participanți

Comunitatea română a programatorilor MySQL.