Come vedo tutte le chiavi esterne in una tabella o colonna?

In MySQL, come ottengo un elenco di tutti i vincoli di chiave esterna che puntano a una tabella particolare? una colonna particolare? Questa è la stessa cosa di questa domanda Oracle , ma per MySQL.

Per un tavolo:

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

Per una colonna:

 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 = '' AND REFERENCED_COLUMN_NAME = '';

Fondamentalmente, abbiamo modificato REFERENCED_TABLE_NAME con REFERENCED_COLUMN_NAME nella clausola where.

EDIT: Come sottolineato nei commenti, questa non è la risposta corretta alla domanda OPs, ma è utile conoscere questo comando. Questa domanda è apparsa su Google per quello che stavo cercando, e ho pensato che avrei lasciato questa risposta per gli altri.

 SHOW CREATE TABLE ``; 

Ho trovato questa risposta qui: MySQL: mostra i vincoli sul comando delle tabelle

Mi serviva in questo modo perché volevo vedere come funzionava il FK, piuttosto che vedere se esisteva o meno.

Se si utilizza InnoDB e FK definiti è ansible interrogare il database information_schema ad esempio:

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

Pubblicando su una vecchia risposta per aggiungere alcune informazioni utili.

Ho avuto un problema simile, ma volevo anche vedere il CONSTRAINT_TYPE insieme ai nomi delle tabelle e delle colonne REFERENCED. Così,

  1. Per vedere tutti i FK nella tua tabella:

     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. Per vedere tutte le tabelle e gli FK nello 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. Per vedere tutti gli FK nel tuo 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'; 

Ricorda!

Questo sta utilizzando il motore di archiviazione InnoDB. Se non riesci a ottenere chiavi esterne da mostrare dopo averle aggiunte probabilmente è perché le tue tabelle utilizzano MyISAM.

Controllare:

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

Per risolvere, utilizzare questo:

 ALTER TABLE `` ENGINE=InnoDB; 

In alternativa alla risposta di Node, se si utilizza InnoDB e FK definiti è ansible interrogare il database information_schema ad esempio:

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

per le chiavi esterne da , o

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

per le chiavi esterne a

Puoi anche ottenere UPDATE_RULE e DELETE_RULE se lo desideri.

Questa soluzione non mostrerà solo tutte le relazioni, ma anche il nome del vincolo, che è richiesto in alcuni casi (es.

 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; 

Se si desidera controllare le tabelle in un database specifico, alla fine della query aggiungere il nome della tabella:

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

Allo stesso modo, per un nome colonna specifico, aggiungere

e table_name = ‘nome_tabella

alla fine della query.

Ispirato da questo post qui

Un modo rapido per elencare i tuoi FK (riferimenti a chiave esterna) usando il

 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; 

Questa query presuppone che i vincoli e tutte le tabelle referenziate e di riferimento siano nello stesso schema.

Aggiungi il tuo commento.

Fonte: il manuale ufficiale di mysql.

Per trovare tutte le tabelle contenenti una particolare chiave esterna come employee_id

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

L’utilizzo di REFERENCED_TABLE_NAME non funziona sempre e può essere un valore NULL. La seguente query può invece funzionare:

 select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '';

La soluzione che ho trovato è fragile; si basa sulla convenzione di denominazione di Django per le chiavi esterne.

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

Quindi, nella shell,

 grep 'refs_tablename_id' mysql_output 

Se si desidera anche ottenere il nome della colonna della chiave esterna:

 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 = '' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY i.TABLE_NAME;