pengembangan-web-mp.com

Bagaimana cara melihat semua kunci asing ke tabel atau kolom?

Di MySQL, bagaimana saya mendapatkan daftar semua batasan kunci asing yang menunjuk ke tabel tertentu? kolom tertentu? Ini adalah hal yang sama dengan pertanyaan Oracle ini , tetapi untuk MySQL.

474
Christian Oudard

Untuk Meja:

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

Untuk Kolom:

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

Pada dasarnya, kami mengubah REFERENCED_TABLE_NAME dengan REFERENCED_COLUMN_NAME dalam klausa where.

643
Vinko Vrsalovic

EDIT: Seperti yang ditunjukkan dalam komentar, ini bukan jawaban yang benar untuk pertanyaan OPs, tetapi berguna untuk mengetahui perintah ini. Pertanyaan ini muncul di Google untuk apa yang saya cari, dan saya pikir saya akan meninggalkan jawaban ini untuk orang lain temukan.

SHOW CREATE TABLE `<yourtable>`;

Saya menemukan jawaban ini di sini: MySQL: tampilkan batasan pada perintah tabel

Saya membutuhkan cara ini karena saya ingin melihat bagaimana fungsi FK, bukan hanya melihat apakah ada atau tidak.

220
CenterOrbit

Jika Anda menggunakan InnoDB dan menentukan milik FK, Anda dapat menanyakan basis data information_schema mis .:

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

Posting pada jawaban lama untuk menambahkan beberapa informasi berguna.

Saya memiliki masalah yang sama, tetapi saya juga ingin melihat CONSTRAINT_TYPE bersama dengan tabel dan kolom nama yang direferensikan. Begitu,

  1. Untuk melihat semua FK di tabel Anda:

    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. Untuk melihat semua tabel dan FK di skema Anda:

    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. Untuk melihat semua FK di database Anda:

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

Ingat!

Ini menggunakan mesin penyimpanan InnoDB. Jika Anda sepertinya tidak bisa mendapatkan kunci asing untuk muncul setelah menambahkannya, itu mungkin karena tabel Anda menggunakan MyISAM. 

Untuk memeriksa:

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

Untuk memperbaikinya, gunakan ini:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;
41
Andy

Sebagai alternatif untuk jawaban Node, jika Anda menggunakan InnoDB dan mendefinisikan FK, Anda dapat menanyakan database information_schema misalnya:

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

untuk kunci asing dari <table>, atau

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

untuk kunci asing ke <tabel>

Anda juga bisa mendapatkan UPDATE_RULE dan DELETE_RULE jika Anda menginginkannya.

21
ChrisV

Solusi ini tidak hanya akan menampilkan semua hubungan tetapi juga nama kendala, yang diperlukan dalam beberapa kasus (mis. 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;

Jika Anda ingin memeriksa tabel dalam database tertentu, di akhir kueri tambahkan nama skema:

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

Demikian juga, untuk nama kolom tertentu, tambahkan

dan table_name = 'table_name

di akhir kueri.

Terinspirasi oleh pos ini di sini

8
Panayotis

Cara cepat untuk mendaftar FK Anda (referensi Kunci Asing) menggunakan 

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;

Kueri ini mengasumsikan bahwa kendala dan semua tabel referensi dan referensi berada dalam skema yang sama.

Tambahkan komentar Anda sendiri.

Sumber: manual resmi mysql.

3
Daniel Rodas

Menggunakan REFERENCED_TABLE_NAME tidak selalu berfungsi dan bisa menjadi nilai NULL. Sebaliknya, kueri berikut dapat berfungsi:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';
3
Hazok

Untuk menemukan semua tabel yang berisi kunci asing tertentu seperti employee_id

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

Solusi yang saya temukan adalah rapuh; itu bergantung pada konvensi penamaan Django untuk kunci asing.

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

Kemudian, di Shell,

grep 'refs_tablename_id' mysql_output
2
Christian Oudard

Jika Anda juga ingin mendapatkan nama kolom kunci asing:

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
omarjebari