phpMyAdmin | Different methods to View Table Constraints

Author - Ishpreet Kaur

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:

2001
Figure 1

The table structure for colleges and students are shown in following screenshots:

2002
Figure 2

2003
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:

2004
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:

2005
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:

2006
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:

2007
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:

2008
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:
2009
Figure 9

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *