如何通过SQL查询表列及完整的主键、外键信息？

Hey, I totally get where you're coming from—information_schema.COLUMNS gives you most column metadata, but it falls short when it comes to foreign keys. Let's break down how to get both primary key (PK) and foreign key (FK) info for your table myTable in myDbName:

1. Confirm Primary Keys (You Already Know This, But Let's Formalize It) #

You're right that the COLUMN_KEY field with value PRI identifies primary key columns. To pull just the PK details cleanly:

SELECT 
  COLUMN_NAME AS primary_key_column,
  ORDINAL_POSITION AS column_order,
  DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'myDbName' 
  AND TABLE_NAME = 'myTable'
  AND COLUMN_KEY = 'PRI';

2. Fetch Foreign Key Information #

For foreign keys, you need to use the information_schema.KEY_COLUMN_USAGE view—it's built specifically to track key constraints, including FK relationships. Here's a query to get all FK details for your table:

SELECT 
  COLUMN_NAME AS foreign_key_column,
  ORDINAL_POSITION AS column_order,
  REFERENCED_TABLE_NAME AS referenced_table,
  REFERENCED_COLUMN_NAME AS referenced_column,
  CONSTRAINT_NAME AS foreign_key_constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'myDbName'
  AND TABLE_NAME = 'myTable'
  AND REFERENCED_TABLE_NAME IS NOT NULL; -- Filters out non-FK keys like unique constraints

This returns exactly which columns are foreign keys, which table/column they reference, and the name of the FK constraint itself.

3. Combine All Column Metadata (PK + FK + Basic Details) #

If you want a single query that shows every column's basic info, whether it's a PK, and any FK relationships, use a left join between COLUMNS and KEY_COLUMN_USAGE:

SELECT 
  c.COLUMN_NAME,
  c.ORDINAL_POSITION,
  c.DATA_TYPE,
  c.IS_NULLABLE,
  CASE WHEN c.COLUMN_KEY = 'PRI' THEN 'YES' ELSE 'NO' END AS is_primary_key,
  kcu.REFERENCED_TABLE_NAME AS fk_references_table,
  kcu.REFERENCED_COLUMN_NAME AS fk_references_column,
  kcu.CONSTRAINT_NAME AS fk_constraint_name
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE kcu
  ON c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  AND c.TABLE_NAME = kcu.TABLE_NAME
  AND c.COLUMN_NAME = kcu.COLUMN_NAME
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
WHERE c.TABLE_SCHEMA = 'myDbName'
  AND c.TABLE_NAME = 'myTable'
ORDER BY c.ORDINAL_POSITION;

This gives you a comprehensive overview: every column in order, its data type, nullability, PK status, and any FK references (if applicable).

内容的提问来源于stack exchange，提问作者Morgan