Wie kann ich alle Fremdschlüssel zu einer Tabelle oder Spalte sehen?

Question

Wie erhalte ich in MySQL eine Liste aller Fremdschlüssel-Beschränkungen, die auf eine bestimmte Tabelle oder eine bestimmte Spalte verweisen? Das ist dasselbe wie diese Oracle-Frage sondern für MySQL.

Answer 1

Für einen Tisch:

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

Für eine Kolumne:

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

Im Grunde haben wir REFERENCED_TABLE_NAME durch REFERENCED_COLUMN_NAME in der where-Klausel ersetzt.

Answer 2

EDIT: Wie in den Kommentaren erwähnt, ist dies nicht die richtige Antwort auf die Frage des Auftraggebers, aber es ist nützlich, diesen Befehl zu kennen. Diese Frage zeigte sich in Google für das, was ich suchte, und dachte, ich würde diese Antwort für die anderen zu finden verlassen.

SHOW CREATE TABLE `<yourtable>`;

Ich habe diese Antwort hier gefunden: MySQL : Befehl show constraints on tables

Ich brauchte diesen Weg, weil ich sehen wollte, wie die FK funktionierte, und nicht nur, ob sie existierte oder nicht.

Answer 3

Wenn Sie InnoDB und definierte FK's verwenden, können Sie z.B. die information_schema Datenbank abfragen:

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

Answer 4

Ich schreibe auf eine alte Antwort, um einige nützliche Informationen hinzuzufügen.

Ich hatte ein ähnliches Problem, aber ich wollte auch den CONSTRAINT_TYPE zusammen mit den REFERENCED-Tabellen- und Spaltennamen sehen. Also,

1. Um alle FKs in Ihrer Tabelle zu sehen:

   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. Um alle Tabellen und FKs in Ihrem Schema zu sehen:

   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. Um alle FKs in Ihrer Datenbank zu sehen:

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

Erinnern Sie sich!

Dabei wird die InnoDB-Speicher-Engine verwendet. Wenn nach dem Hinzufügen keine Fremdschlüssel angezeigt werden, liegt das wahrscheinlich daran, dass Ihre Tabellen MyISAM verwenden.

Zur Kontrolle:

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

Verwenden Sie zur Behebung dies:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Answer 5

Als Alternative zu Node's Antwort, wenn Sie InnoDB und definierte FK's verwenden, könnten Sie die information_schema Datenbank z.B. abfragen:

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

für Fremdschlüssel aus <Tabelle>, oder

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

für Fremdschlüssel zu <Tabelle>

Sie können auch die UPDATE_RULE und DELETE_RULE erhalten, wenn Sie diese benötigen.