Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Get the fields in each constraint in MySQL
Let’s say we have a database “business” with number of tables. If you want to Get the fields in each constraint, then use the below query.
The below query is to get the fields in each one of those constraints −
mysql> select * −> from information_schema.key_column_usage −> where constraint_schema = 'business';
The following is the output −
+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | business | PRIMARY | def | business | primarytable | FKPK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | primarytable1 | Fk_pk | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrementtable | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | demoauto | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrement | IdAuto | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | primarytabledemo | FK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | sequencedemo | SequenceId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | updtable | IncId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | transcationdemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | triggedemo | User_id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | usernameandpassworddemo | U_Id | 1 | NULL | NULL | NULL | NULL | | def | business | UserId | def | business | usernameandpassworddemo | UserId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | tblp | FK_PK | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | uniquedemo | name | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | uniqueconstdemo | name | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | keydemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | nextiddemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | tablepri | id | 1 | NULL | NULL | NULL | NULL | | def | business | ConstFK | def | business | tabledemo2 | id | 1 | NULL | NULL | NULL | NULL | | def | business | ConstFK | def | business | tabledemo3 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | college | StudentFKPK | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | id | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | name | 2 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniqueautoid | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | schemadatabasemethoddemo | TheKey | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | employeeinformation | EmpId | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | addingunique | name | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | parentdemo | FKPK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | lastinsertrecordiddemo | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | demoindex | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | StudentName | 2 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | addingautoincrement | Id | 1 | NULL | NULL | NULL | NULL | | def | business | Id | def | business | uniqueconstrainttable | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | mergedemo1 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | mergedemo2 | id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | foreigntable1 | StudentId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | twoprimarykeytabledemo | Result | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | twoprimarykeytabledemo | StudentId | 2 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | showconstraintsdemo | BookId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | showconstraintsdemo | BookName | 2 | NULL | NULL | NULL | NULL | | def | business | BookAuthor | def | business | showconstraintsdemo | BookAuthor | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrementtozero | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | altertabletoaddautoincrement | StudentId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | addingprimarykeydemo | UniversityId | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | resetprimarykey | Id | 1 | NULL | NULL | NULL | NULL | | def | business | constFKPK | def | business | foreigntable | Fk_pk | 1 | 1 | business | primarytable1 | fk_pk | | def | business | FKConst | def | business | foreigntabledemo | FK | 1 | 1 | business | primarytabledemo | fk | | def | business | ConstFK | def | business | tblf | FK_PK | 1 | 1 | business | tblp | fk_pk | | def | business | StudCollegeConst | def | business | studentenrollment | StudentFKPK | 1 | 1 | business | college | studentfkpk | | def | business | ConstChild | def | business | childdemo | FKPK | 1 | 1 | business | parentdemo | fkpk | | def | business | primarytable1demo_ibfk_1 | def | business | primarytable1demo | ForeignId | 1 | 1 | business | foreigntable1 | studentid | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ 52 rows in set, 2 warnings (0.21 sec)
Updated on: 30-Jul-2019
36 Views
Advertisements