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
How do I see all foreign keys to a table column?
To see all the foreign keys to a table or column, the referenced_column_name command is used.
First, two tables are created and then related with the help of the foreign key constraint.
Creating the first table −
mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows affected (0.43 sec)
After creating the first table successfully, the second table is created as follows −
mysql> CREATE table primaryTable1 -> ( -> Fk_pk int, -> DeptName varchar(200), -> Primary key(Fk_pk) -> ); Query OK, 0 rows affected (0.48 sec)
Now, both the tables are related with the help of the alter command and the foreign key constraint is also added. The syntax for this is as follows −
alter table yourFirstTable add constraint anyConstraintName foreign key(column_name which is acts foreign key in second table) yourSecondTable(column_name which acts primary key in second table).
The above syntax is applied to relate both the tables as follows −
mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references primaryTable1(Fk_pk); Query OK, 0 rows affected (1.57 sec) Records: 0 Duplicates: 0 Warnings: 0
Now, the syntax to see all the foreign keys to a table is given as follows −
For a table −
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'yourReferencedTableName';
Now the above syntax is used to create the query to view all the foreign keys. The query is given as follows −
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE -> WHERE REFERENCED_TABLE_NAME = 'primarytable1';
The following is the output −
+--------------+-------------+-----------------+-----------------------+------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------+-------------+-----------------+-----------------------+------------------------+ | foreigntable | Fk_pk | constFKPK | primarytable1 | fk_pk | +--------------+-------------+-----------------+-----------------------+------------------------+ 1 row in set, 2 warnings (0.02 sec)
In the sample output, the constraint_name is ‘constFKPK’ and table_name is ‘foreigntable’. 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 = 'yourDatabaseName' AND REFERENCED_TABLE_NAME = 'yourreferencedtablename' AND REFERENCED_COLUMN_NAME = 'yourreferencedcolumnname';
The query to display all the foreign keys to a column is given using the above syntax. The query is as follows −
mysql> SELECT -> TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME -> FROM -> INFORMATION_SCHEMA.KEY_COLUMN_USAGE -> WHERE -> REFERENCED_TABLE_SCHEMA = 'business' AND -> REFERENCED_TABLE_NAME = 'primarytable1' AND REFERENCED_COLUMN_NAME = 'fk_pk';
The output obtained is as follows:
+--------------+-------------+-----------------+-----------------------+------------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME |REFERENCED_COLUMN_NAME | +--------------+-------------+-----------------+-----------------------+------------------------+ | foreigntable | Fk_pk | constFKPK | primarytable1 | fk_pk | +--------------+-------------+-----------------+-----------------------+------------------------+ 1 row in set, 2 warnings (0.03 sec)
2K+ Views
