- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- Get number of fields in MySQL table?
- Get the count of two table fields in a single MySQL query?
- Add DATE and TIME fields to get DATETIME field in MySQL?
- Substring() for Fields in MySQL?
- How to apply Substring() for fields in MySQL to get part of string?
- Adding unique constraint to ALTER TABLE in MySQL
- Get the fields of the current Type in C#
- How to concatenate fields in MySQL?
- MySQL find/ replace string in fields?
- Get the list of all declared fields in Java
- Add constraint for on duplicate key update in MySQL
- Get the list of all the public fields in Java
- How to display the bit(1) fields in MySQL?
- Dropping Unique constraint from MySQL table?
- How to mask data fields in MySQL?
Advertisements