मैं सभी विदेशी कुंजी को किसी तालिका या कॉलम में कैसे देखूं?

MySQL में, मैं किसी विशेष तालिका को इंगित करने वाली सभी विदेशी कुंजी बाधाओं की सूची कैसे प्राप्त करूं? एक विशेष स्तंभ? यह वही बात है जैसे यह ओरेकल प्रश्न , लेकिन MySQL के लिए।

413

11 उत्तर

किसी तालिका के लिए:

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
जोड़ा
@Acute: क्या आप वाकई सही टेबल के बारे में पूछ रहे हैं? यदि नोड की क्वेरी काम करती है, तो आप शायद दूसरी दिशा (यानी, Mytable से चाबियाँ, Mytable के लिए चाबियाँ नहीं) के बारे में पूछ रहे हैं। यह उम्मीद है कि आपने टेबल नाम के साथ '<table>' लिखा है और '<' और '>' के बिना ?
जोड़ा लेखक Vinko Vrsalovic, स्रोत
इसे इतना क्यों जरूरी है? आप पंक्तियों का चयन कैसे करते हैं उतना आसान क्यों नहीं बनाते?
जोड़ा लेखक Lealo, स्रोत
सहमत हुए, मुझे कभी भी एक खाली सेट मिल गया है! एंडी के जवाब ने मेरे लिए सबसे अच्छा काम किया।
जोड़ा लेखक omarjebari, स्रोत
@ दीपकक्राम यह मेरे लिए विशेषाधिकार प्राप्त उपयोगकर्ता के साथ पूरी तरह से काम करता है जो मूल उपयोगकर्ता नहीं है।
जोड़ा लेखक Matthew Read, स्रोत
यह मेरे लिए सही काम किया। मेरे पास सबसे अधिक विशेषाधिकार हैं, लेकिन रूट नहीं है। आप एक जीवन बचतकर्ता हैं।
जोड़ा लेखक jDub9, स्रोत
यह हमेशा मुझे एक खाली सेट देता है, जबकि नीचे नोड द्वारा प्रस्तावित क्वेरी ठीक काम करती है
जोड़ा लेखक Acute, स्रोत
ऐसा लगता है कि मैंने आपको क्वेरी गलत समझा, क्योंकि मैं <table> :) से संदर्भित कुंजी के लिए पूछताछ कर रहा था (हाँ, मैंने "<table>" XD के बजाय तालिका का नाम लिखा था)
जोड़ा लेखक Acute, स्रोत
जब तक आप सुनिश्चित न हों कि आपका टेबल नाम अद्वितीय है, तो आप शायद अपनी क्वेरी को किसी विशेष डेटाबेस पर भी सीमित करना चाहेंगे। इस पर खंड कहां बदलें: जहां REFERENCED_TABLE_SCHEMA = 'mydatabase' और REFERENCED_TABLE_NAME = 'mytable'
जोड़ा लेखक user12345, स्रोत
यह गैर रूट उपयोगकर्ता के मामले में काम नहीं करेगा भले ही उपयोगकर्ता को डेटाबेस में सभी अनुमतियां हों
जोड़ा लेखक Deepak Ram, स्रोत

संपादित करें: टिप्पणियों में बताया गया है, यह ओपीएस प्रश्न का सही उत्तर नहीं है, लेकिन यह आदेश जानने के लिए उपयोगी है। यह प्रश्न Google में जो मैंने खोजा था, उसके लिए दिखाया गया, और लगा कि मैं दूसरों के लिए यह जवाब छोड़ दूंगा।

SHOW CREATE TABLE ``;

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

मुझे इस तरह से जरूरी था क्योंकि मैं देखना चाहता था कि एफके कैसे काम करता है, सिर्फ यह देखने के बजाय कि यह अस्तित्व में है या नहीं।

194
जोड़ा
जैसा कि @ बाड़मेर कहते हैं, यह पूरी तरह गलत है; यह निर्दिष्ट तालिका से संबंधित विदेशी कुंजी दिखाएगा, लेकिन तालिका को TO इंगित करने वाली विदेशी कुंजी नहीं दिखाएगी, जो प्रश्न पूछता है। कोई विचार नहीं कि यह 50 अपवॉट कैसे प्राप्त हुआ; मुझे लगता है कि लोग यहां समाप्त हुए थे जब वास्तव में वे विपरीत प्रश्न के उत्तर की तलाश में थे, वैसे भी उनका जवाब यहां मिला, और ऊपर उठने से पहले मूल प्रश्न (या यहां तक ​​कि इसके शीर्षक) को पढ़ने से परेशान नहीं किया।
जोड़ा लेखक Mark Amery, स्रोत
यह में सभी बाधाओं को दिखाता है, सभी बाधाओं को नहीं जो पर इंगित करता है।
जोड़ा लेखक Barmar, स्रोत
यह वास्तव में मौजूद सभी बाधाओं को दिखाता है, phpmyadmin केवल आपको अपनी तालिका में इंगित करने वाला एक दिखाएगा। एक ओआरएम उपकरण के साथ डुप्लिकेट से बचने के लिए पर्याप्त अच्छा लगता है
जोड़ा लेखक william.eyidi, स्रोत
@MarkAmery: यह Google में विदेशी कुंजी MySQL प्रदर्शित करने का पहला परिणाम है, यही कारण है कि;)
जोड़ा लेखक Jigar, स्रोत
यही वह है जिसे मैं ढूंढ रहा था, इसलिए इसे पोस्ट करने के लिए धन्यवाद, भले ही उसने ओपी के सवाल का जवाब नहीं दिया। किसी रिश्ते में दोनों दिशाओं को कैसे देखना है, यह जानने के लिए कभी दर्द नहीं होता!
जोड़ा लेखक Charles Wood, स्रोत

यदि आप इनो डीबी और परिभाषित एफके का उपयोग करते हैं तो आप info_schema डेटाबेस उदास से पूछ सकते हैं .:

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
जोड़ा
वास्तव में, यह गलत दिशा को इंगित करता है। वह क्वेरी 'mytable' से इंगित सभी विदेशी कुंजी दिखाती है, न कि 'mytable' को इंगित करने वाली सभी विदेशी कुंजी।
जोड़ा लेखक Christian Oudard, स्रोत
आप REFERENTIAL_CONSTRAINTS तालिका से दोनों दिशाओं में विदेशी कुंजी प्राप्त कर सकते हैं - मैंने क्वेरी के साथ एक और जवाब जोड़ा है।
जोड़ा लेखक ChrisV, स्रोत
यह मेरे मामले में बेहतर काम करता है। मुझे इनओडीबी इंजन माईसाम या एनडीबी को बदलने में सक्षम होने के लिए तालिका से प्रत्येक विदेशी कुंजी बाधा (और केवल उन) को छोड़ने की आवश्यकता है।
जोड़ा लेखक Kohányi Róbert, स्रोत

कुछ उपयोगी जानकारी जोड़ने के लिए पुराने उत्तर पर पोस्ट करना।

मुझे एक ही समस्या थी, लेकिन मैं CONSTRAINT_TYPE को संदर्भित तालिका और कॉलम नामों के साथ भी देखना चाहता था। इसलिए,

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

याद रखें!

यह इनो डीबी स्टोरेज इंजन का उपयोग कर रहा है। यदि आपको उन्हें जोड़ने के बाद दिखाने के लिए कोई विदेशी कुंजी नहीं मिलती है तो शायद यह है कि आपकी टेबल MyISAM का उपयोग कर रही हैं।

जाँच करने के लिए:

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

ठीक करने के लिए, इसका उपयोग करें:

ALTER TABLE `` ENGINE=InnoDB;
32
जोड़ा
दुर्भाग्य से, MyISAM विदेशी कुंजी का समर्थन नहीं करता है। dev.mysql.com/doc/refman/5.7/ एन/MyISAM-भंडारण-engine.html
जोड़ा लेखक Andy, स्रोत
बहुत बढ़िया जवाब। क्या आपके पास माईसाम के लिए कोई समाधान है?
जोड़ा लेखक Beau Bouchard, स्रोत
यदि आप WHERE क्लॉज पर k.TABLE_SCHEMA = DATABASE() और k.TABLE_NAME = '<table>' निर्दिष्ट करते हैं, तो वे प्रश्न dev.mysql.com/doc/refman/5.5/en/…
जोड़ा लेखक guigouz, स्रोत

नोड के उत्तर के विकल्प के रूप में, यदि आप इनो डीबी और परिभाषित एफके का उपयोग करते हैं तो आप info_schema डेटाबेस उदास से पूछ सकते हैं .:

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
जोड़ा
मैं व्यक्तिगत रूप से इस उत्तर को प्राथमिकता देता हूं क्योंकि REFERENTIAL_CONSTRAINTS तालिका आपको अद्यतन और कैस्केड नियम देता है। +1
जोड़ा लेखक Luke Madhanga, स्रोत
एक टेबल के बारे में खोज करना आपको यह नहीं भूलना चाहिए कि विदेशी चाबियाँ स्थापित की जा सकती हैं!
जोड़ा लेखक Encoder, स्रोत

यह समाधान न केवल सभी संबंधों को प्रदर्शित करेगा, बल्कि बाधा नाम भी होगा, जो कुछ मामलों में आवश्यक है (उदा। ड्रॉप अवरोध):

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;

यदि आप किसी विशिष्ट डेटाबेस में टेबल को जांचना चाहते हैं, तो क्वेरी के अंत में तालिका का नाम जोड़ें:

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

इसी तरह, एक विशिष्ट कॉलम नाम के लिए, जोड़ें

और table_name = 'table_name

प्रश्न के अंत में।

Inspired by this post here

7
जोड़ा

का उपयोग कर अपने एफके (विदेशी कुंजी संदर्भ) को सूचीबद्ध करने का एक त्वरित तरीका

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;

यह क्वेरी मानती है कि बाधाएं और सभी संदर्भित और संदर्भ तालिका समान स्कीमा में हैं।

अपनी खुद की टिप्पणी जोड़ें।

स्रोत: आधिकारिक MySQL मैनुअल।

2
जोड़ा

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
जोड़ा

जिस समाधान के साथ मैं आया था वह नाजुक है; यह विदेशी कुंजी के लिए django के नामकरण सम्मेलन पर निर्भर करता है।

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

फिर, खोल में,

grep 'refs_tablename_id' mysql_output
1
जोड़ा

REFERENCED_TABLE_NAME का उपयोग करना हमेशा काम नहीं करता है और यह एक पूर्ण मान हो सकता है। निम्न क्वेरी इसके बजाय काम कर सकती है:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '<table>';
1
जोड़ा

यदि आप विदेशी कुंजी कॉलम का नाम भी प्राप्त करना चाहते हैं:

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
जोड़ा