:How to view the primary key fields of a table in apsaradb for MySQL

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

 

Overview

This topic describes how to view the primary key fields of a table in apsaradb for MySQL.

 

Description

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

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]';

Note:

• [$Table_Name] is the table name.
• [$DB_Name] is the name of the database where the table is located.

 

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];

 

Application scope

• ApsaraDB RDS for MySQL