如何查询表的主键和索引

查询数据库表的主键和索引可以通过不同的数据库管理系统（DBMS）以不同的方式完成。以下是一些常见数据库系统中查询主键和索引的方法：

MySQL

1. 查询主键：

```sql

SELECT KU.Column_name

FROM information_schema.key_column_usage KU

WHERE KU.table_schema = 'your_database_name'

AND KU.table_name = 'your_table_name'

AND KU.constraint_name = 'PRIMARY';

```

2. 查询索引：

```sql

SELECT

FROM information_schema.statistics

WHERE table_schema = 'your_database_name'

AND table_name = 'your_table_name';

```

PostgreSQL

1. 查询主键：

```sql

SELECT a.attname

FROM pg_attribute a

JOIN pg_class c ON a.attrelid = c.oid

JOIN pg_index i ON c.oid = i.indrelid

WHERE c.relkind = 'r'

AND a.attnum > 0

AND NOT a.attisdropped

AND i.indisprimary;

```

2. 查询索引：

```sql

SELECT

FROM pg_indexes

WHERE schemaname = 'public' -替换为你的schema名称

AND tablename = 'your_table_name';

```

SQL Server

1. 查询主键：

```sql

SELECT

KCU.name AS 'Key_Column_Name',

KC.name AS 'Key_Name'

FROM

sys.key_constraints AS KC

INNER JOIN sys.key_column_usage AS KCU

ON KC.object_id = KCU.constraint_object_id

WHERE

KC.type = 'PK'

AND OBJECT_NAME(KC.parent_object_id) = 'your_table_name';

```

2. 查询索引：

```sql

SELECT

i.name AS IndexName,

COL_NAME(ic.object_id, ic.column_id) AS ColumnName

FROM

sys.indexes AS i

INNER JOIN sys.index_columns AS ic

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

WHERE

i.object_id = OBJECT_ID('your_table_name');

```

Oracle

1. 查询主键：

```sql

SELECT

COLUMN_NAME

FROM

USER_CONS_COLUMNS

WHERE

TABLE_NAME = 'YOUR_TABLE_NAME'

AND CONSTRAINT_NAME = 'YOUR_PRIMARY_KEY_CONSTRAINT_NAME';

```

2. 查询索引：

```sql

SELECT

INDEX_NAME,

COLUMN_NAME

FROM

USER_IND_COLUMNS

WHERE

TABLE_NAME = 'YOUR_TABLE_NAME';

```

在执行上述查询时，请确保将 `'your_database_name'`, `'your_table_name'`, `'your_schema_name'`, `'YOUR_PRIMARY_KEY_CONSTRAINT_NAME'` 等占位符替换为实际的数据库名称、表名称、模式名称和主键约束名称。