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

Eine schnelle Möglichkeit, Ihre FKs (Foreign Key references) aufzulisten, ist die Verwendung der

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;

Bei dieser Abfrage wird davon ausgegangen, dass die Constraints und alle referenzierten und referenzierenden Tabellen im selben Schema sind.

Fügen Sie Ihren eigenen Kommentar hinzu.

Quelle: das offizielle mysql-Handbuch.

Answer 2

Die Lösung, die ich mir ausgedacht habe, ist anfällig; sie beruht auf der Namenskonvention von Django für Fremdschlüssel.

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

Dann, in der Schale,

grep 'refs_tablename_id' mysql_output

Answer 3

Wenn Sie auch den Namen der Fremdschlüsselspalte erhalten möchten:

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;

Answer 4

Ich brauchte eine Vogelperspektive auf die Beziehungen zwischen den Tabellen (zur Verwendung in einem ORM). Anhand der Vorschläge auf dieser Seite und nach einigen Experimenten habe ich die folgende Abfrage erstellt:

SELECT
    KCU.CONSTRAINT_NAME,
    KCU.TABLE_NAME,
    KCU.COLUMN_NAME,
    KCU.REFERENCED_TABLE_NAME,
    KCU.REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    JOIN INFORMATION_SCHEMA.COLUMNS AS COLS
        ON
                COLS.TABLE_SCHEMA = KCU.TABLE_SCHEMA
            AND COLS.TABLE_NAME   = KCU.TABLE_NAME
            AND COLS.COLUMN_NAME  = KCU.COLUMN_NAME
WHERE
        KCU.CONSTRAINT_SCHEMA = {YOUR_SCHEMA_NAME}
    AND KCU.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
    KCU.TABLE_NAME,
    COLS.ORDINAL_POSITION

Sie liefert genau das, was ich brauche, und zwar in der gewünschten Reihenfolge.

Ich bearbeite das Ergebnis auch ein wenig (verwandle es in eine Art Wörterbuch), so dass es für die Erstellung eines Aggregats verwendet werden kann.

Answer 5

So finden Sie alle Tabellen die einen bestimmten Fremdschlüssel enthalten wie zum Beispiel employee_id

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