How to view List of constraints of tables of Database in phpMyAdmin
There are various ways by which we can view the list of constraints applied to tables of given database in PhpMyadmin. Let’s see few methods here:
Suppose we have a database named CollegeDb having two tables colleges and students:
Figure 1
The table structure for colleges and students are shown in following screenshots:
Figure 2
Figure 3
1. View applied constraints through Schema query:
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'CollegeDb'
The output of above query is shown below:
Figure 4
From Figure 4, we can see that two constraints viz PRIMARY and UNIQUE key constraints have been applied to table colleges and three constraints viz PRIMARY, UNIQUE and FOREIGN key constraints have been applied to students table.
We can also view list of constraints for only single table with the following query:
SELECT * FROM information_schema.table_constraints WHERE table_name = 'colleges'
With the above query, the output will be displayed in same form as shown in Figure 4, but records will be of given table ie. colleges table only.
2. Relation View: With Relation view link at bottom of table, for example students in our case, will display only foreign key constraints.
Relation view link is shown in Figure 3.
Click on Relation view link to view foreign key constraint(s) for given table.
The following screenshot will be displayed:
Figure 5
With this approach, only foreign key constraints can be viewed of the selected table.
3. Print View: A print View link is displayed at the bottom of table and to the left of Relation view Link as shown in Figure 3. On clicking of Print View Link, following screen will be displayed:
Figure 6
Three Constraints have been shown for students table:
PRIMARY (id),
UNIQUE (college_id, email),
FOREIGN KEY(college_id) REFERENCES colleges(id)
Similarly, when we click on Print view for colleges table, then only two constraints will be displayed:
PRIMARY (id),
UNIQUE (name) //college name will be in the table only once.
4. Through “Show create table” query: When we write the query as:
SHOW CREATE TABLE colleges;
the above query will generate following screenshot:
Figure 7
In the above Figure 7, we can see that Primary Key and unique key constraints are shown in create table query for colleges.
Sometimes, the whole query is not displayed, instead, it gets trimmed like in students table as shown in following:
Figure 8
As shown in Figure 8, the complete query is not displaying. For the complete query, click on Print view (with full texts).
Now, we can view complete query as shown below:
Figure 9