Generally speaking, most tables we create in MySQL should have a primary key. A primary key is one or more columns that have been configured as the unique identifier for the table.
We usually create the primary key when creating the table, but we can also add a primary key to an existing table (assuming it doesn’t already have one).
Occasionally it might not be immediately apparent which column is the primary key for a given table. Or it might not be apparent whether or not the table has a composite primary key. Fortunately it’s easy enough to find out.
Below are five ways to get the primary key column/s from an existing table in MySQL.
The SHOW KEYS Statement
In MySQL we can use the SHOW KEYS statement to get the primary key for a given table:
SHOW KEYS FROM Person
WHERE Key_name = 'PRIMARY';Example result:
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Person | 0 | PRIMARY | 1 | PersonId | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | | Person | 0 | PRIMARY | 2 | VoucherCode | A | 2 | NULL | NULL | | BTREE | | | YES | NULL | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.01 sec)
Primary keys always have a Key_name of PRIMARY, and so that’s why I filtered by that criteria.
In this example, the primary key is a composite primary key (it’s made up of multiple columns). In this case the primary key comes from two columns: the PersonId column and the VoucherCode column. That’s why two rows were returned.
In MySQL, SHOW KEYS, SHOW INDEX, and SHOW INDEXES are synonyms. So in the above example we can replace KEYS with INDEX or INDEXES and we’d get the same result.
The information_schema.columns Table
Another way to do it is to query the information_schema.columns table:
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 'Person'
AND column_key = 'PRI';Example result:
+-------------+ | COLUMN_NAME | +-------------+ | PersonId | | VoucherCode | +-------------+
The column_key column is always PRI for any primary key columns. Given our table has a composite primary key (consisting of two columns), we get two rows returned here.
The information_schema.statistics Table
We can alternatively query the information_schema.statistics table:
SELECT column_name
FROM information_schema.statistics
WHERE table_schema = 'test'
AND table_name = 'Person'
AND index_name = 'PRIMARY';Example result:
+-------------+ | COLUMN_NAME | +-------------+ | PersonId | | VoucherCode | +-------------+
In this table, the index_name column is always PRIMARY for any primary key columns.
The SHOW CREATE TABLE Statement
We can use the SHOW CREATE TABLE statement to return the SQL statement that would create the table. This includes the code required to create the primary key:
SHOW CREATE TABLE Person;Example result:
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Person | CREATE TABLE `Person` ( `PersonId` int NOT NULL, `VoucherCode` int NOT NULL, `FirstName` varchar(20) DEFAULT NULL, `LastName` varchar(20) DEFAULT NULL, PRIMARY KEY (`PersonId`,`VoucherCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
We can see that the line that begins with PRIMARY KEY is the line that creates the primary key. We can see that it specifies the two columns that will become the primary key.
The mysqlshow Command
We can run the mysqlshow command to get information about a given table. To do this, we need to run mysqlshow from a separate terminal/command window (we don’t run it from the mysql command line client itself).
For example, we can open a new terminal window and enter the following:
mysqlshow -k test Person -urootExample result:
+-------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ | PersonId | int | | NO | PRI | | | select,insert,update,references | | | VoucherCode | int | | NO | PRI | | | select,insert,update,references | | | FirstName | varchar(20) | utf8mb4_0900_ai_ci | YES | | | | select,insert,update,references | | | LastName | varchar(20) | utf8mb4_0900_ai_ci | YES | | | | select,insert,update,references | | +-------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+ +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Person | 0 | PRIMARY | 1 | PersonId | A | 1 | | | | BTREE | | | YES | | | Person | 0 | PRIMARY | 2 | VoucherCode | A | 2 | | | | BTREE | | | YES | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
This returns more than enough information to tell us which columns are the primary keys. The -k option is used to show information about the table’s indexes.