MySQL में फॉरेन की कंस्ट्रेंट की सूची कैसे प्राप्त करें?

MySQL में केवल फॉरेन की कंस्ट्रेंट देखने के लिए information_schema.referential_constraints टेबल क्वेरी करें।

सिंटैक्स

SELECT * FROM information_schema.referential_constraints 
WHERE constraint_schema = 'yourDatabaseName';

उदाहरण: 'business' डेटाबेस

mysql> select * 
-> from information_schema.referential_constraints 
-> where constraint_schema = 'business';

मुख्य कॉलम व्याख्या

नमूना आउटपुट

+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME          | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME        | REFERENCED_TABLE_NAME |
+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+
| def                | business          | ConstChild               | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | childdemo         | parentdemo            |
| def                | business          | ConstFK                  | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | tblf              | tblp                  |
| def                | business          | constFKPK                | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | foreigntable      | primarytable1         |
| def                | business          | FKConst                  | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | foreigntabledemo  | primarytabledemo      |
| def                | business          | primarytable1demo_ibfk_1 | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | primarytable1demo | foreigntable1         |
| def                | business          | StudCollegeConst         | def                       | business                 | PRIMARY                | NONE         | NO ACTION   | NO ACTION   | studentenrollment | college               |
+--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-------------------+-----------------------+

अन्य उपयोगी क्वेरी

-- फॉरेन की कॉलम विवरण के साथ
SELECT 
  rc.CONSTRAINT_NAME,
  rc.TABLE_NAME as child_table,
  kcu.COLUMN_NAME as child_column,
  rc.REFERENCED_TABLE_NAME as parent_table,
  kcu.REFERENCED_COLUMN_NAME as parent_column,
  rc.UPDATE_RULE,
  rc.DELETE_RULE
FROM information_schema.referential_constraints rc
JOIN information_schema.key_column_usage kcu 
  ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
  AND rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE rc.CONSTRAINT_SCHEMA = 'business';