How to view the primary key fields of a table?

When using a MySQL instance, the following three methods are commonly used to view primary key fields. Select a method based on your needs.

View system tables

Run the following SQL statement to confirm the primary key fields in the system table.

SELECT t.TABLE_NAME,
       t.CONSTRAINT_TYPE,
       c.COLUMN_NAME,
       c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE t.TABLE_NAME = c.TABLE_NAME
  AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND t.TABLE_NAME='[$Table_Name]'
  AND t.TABLE_SCHEMA='[$DB_Name]';

View the CREATE TABLE statement

Run the following SQL statement to confirm the primary key fields through the table creation statement.

show create table [$Table_Name];

Query the table schema

Run the following SQL statement to confirm the primary key field based on the table structure.

desc [$Table_Name];