Need to list all primary keys in MySQL database. In this article, you will learn several methods to do that.
1. Using SQL Query
We will begin with our first method, theSQL query. So,to show all primary keys for all tables in the MySQL database, We will be using the following SQL script:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Database_Name' AND COLUMN_KEY = 'PRI' ORDER BY table_name;Sample result
The following is the output of the SQL query:
In addition, if you want to list all primary keys for a specific table in a MySQL database you can use the following SQL query:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Database_Name' AND TABLE_NAME = ‘Table_Name’ AND COLUMN_KEY = 'PRI' ORDER BY table_name;
The output of the query for the actor table:
2. Using MySQL interactive shell command-line
Now let’s see the second method to use to list all primary keys for a specific table in a MySQL database using the interactive shell. we will retrieve the primary key using the SHOW KEYS command or the SHOW INDEX command, as follow:
First command: SHOW KEYS FROM Database_name.Table_name where key_name =’PRIMARY’;
Sample result of the command for the actor table:
Second command: SHOW INDEX FROM Database_name.Table_name WHERE key_name =’PRIMARY’;
Sample result of the command:
3. list all primary keys in MySQL using ERBuilder Data Modeler
Last, we will get a look at ERBuilder Data Modeler which allows you to visualize your database structures in other words this means you will be capable of listing all primary keys for a table in your MySQL database.
Moreover, With ERBuilder Data Modeler, you can get more details of keys just by double-clicking on it from the list. As you can add a new primary key, edit an exciting one, or delete it.
Start modeling your database with us today
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
