How to get primary key of a table in MySQL?

MySQLMySQLi Database

To get the primary key of a table, you can use the show command. The syntax is as follows −

SHOW INDEX FROM yourDatebaseName.yourTableName WHERE Key_name = 'PRIMARY';

Suppose, we have a table with two primary keys; one of them is “Id” and second is “RollNum". The query for a table is as follows −

mysql> create table TwoOrMorePrimary
   −> (
   −> Id int,
   −> Name varchar(200), 
   −> RollNum int
   −> ,
   −> Primary key(Id,Age)
   −> );
Query OK, 0 rows affected (0.85 sec)

Apply the above syntax to get primary key of a table. The query is as follows −

mysql> SHOW INDEX FROM business.TwoOrMorePrimary
−> WHERE Key_name = 'PRIMARY';

The following is the output −

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| twoormoreprimary |          0 | PRIMARY  | 1            | Id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| twoormoreprimary |          0 | PRIMARY  | 2            | RollNum     | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES      | NULL      |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.12 sec)
raja
Published on 10-Jan-2019 12:40:11
Advertisements