PostgreSQL 数据库基础如何查看表的主外键等约束关系详解

在PostgreSQL中，可以使用以下命令查看表的主外键等约束关系：，，``

sql，SELECT，    tc.constraint_name,，    tc.table_name AS table_name,，    kcu.column_name AS foreign_key_column,，    ccu.table_name AS referenced_table_name,，    ccu.column_name AS referenced_column_name，FROM，    information_schema.table_constraints AS tc，JOIN，    information_schema.key_column_usage AS kcu，    ON tc.constraint_name = kcu.constraint_name，JOIN，    information_schema.constraint_column_usage AS ccu，    ON ccu.constraint_name = tc.constraint_name，WHERE，    constraint_type = 'FOREIGN KEY'，ORDER BY，    tc.constraint_name;，

PostgreSQL 数据库基础如何查看表的主外键等约束关系详解

在 PostgreSQL 数据库中，主键和外键是用于确保数据完整性和一致性的重要约束，本篇文章将详细介绍如何在 PostgreSQL 中查看表的主键、外键等约束关系。

1、查看表的主键

要查看表的主键，可以使用以下 SQL 语句：

SELECT a.attname, c.attname, kcu.constraint_name, c.confrelid::regclass AS confrelid
FROM pg_attribute a
JOIN pg_attrdef b ON a.attrelid = b.adrelid AND a.attnum = b.adnum
JOIN pg_constraint c ON b.adoid = c.conrelid
JOIN pg_constraint kcu ON c.conkey = kcu.oid
WHERE a.attnum > 0 AND a.attrelid = '表名'::regclass AND c.contype = 'p' AND kcu.contype = 'p';

表名需要替换为实际的表名，执行该 SQL 语句后，将返回表的主键信息，包括属性名、约束名和对应的参照表。

2、查看表的外键

要查看表的外键，可以使用以下 SQL 语句：

SELECT a.attname, c.attname, kcu.constraint_name, c.confrelid::regclass AS confrelid
FROM pg_attribute a
JOIN pg_attrdef b ON a.attrelid = b.adrelid AND a.attnum = b.adnum
JOIN pg_constraint c ON b.adoid = c.conrelid
JOIN pg_constraint kcu ON c.conkey = kcu.oid
WHERE a.attnum > 0 AND a.attrelid = '表名'::regclass AND c.contype = 'f' AND kcu.contype = 'f';

同样，表名需要替换为实际的表名，执行该 SQL 语句后，将返回表的外键信息，包括属性名、约束名和对应的参照表。

3、查看表的唯一约束

要查看表的唯一约束，可以使用以下 SQL 语句：

SELECT a.attname, c.attname, kcu.constraint_name, c.confrelid::regclass AS confrelid
FROM pg_attribute a
JOIN pg_attrdef b ON a.attrelid = b.adrelid AND a.attnum = b.adnum
JOIN pg_constraint c ON b.adoid = c.conrelid
JOIN pg_constraint kcu ON c.conkey = kcu.oid
WHERE a.attnum > 0 AND a.attrelid = '表名'::regclass AND c.contype = 'u' AND kcu.contype = 'u';

执行该 SQL 语句后，将返回表的唯一约束信息，包括属性名、约束名和对应的参照表。

4、查看表的其他约束

除了主键、外键和唯一约束外，PostgreSQL 还支持其他类型的约束，如检查约束、默认约束等，要查看表的其他约束，可以使用以下 SQL 语句：

SELECT conname, conrelid::regclass AS confrelid, contypid::regtype AS contypid, conind, confdeltype::regtype AS confdeltype, condeferrable, condeferred, conislocal, coninhcount, connowait, convalid, concheckinterval, contablevel, contranslmt, confupdtype::regtype AS confupdtype, confdelcheck, conispartition, conparent, conforcerrant, conispopulated, confmatchtype, confnotnulls, conftarget, conkey, confexcludeprops, confdelay, conqueue, confaction, conqual, confnamespace, confowner, confstarelid::regclass AS confstarelid, confsource::regclass AS confsource, confdestination::regclass AS confdestination, conbin, pgc_get_expr(confexpr, true) AS confexpr, pgc_get_expr(confassrtexpr, true) AS confassrtexpr, pgc_get_expr(confdelexpr, true) AS confdelexpr, pgc_get_expr(confassrtdeferrable, true) AS confassrtdeferrable, pgc_get_expr(confassrtinitdeferred, true) AS confassrtinitdeferred, pgc_get_expr(confassrtcombine, true) AS confassrtcombine, pgc_get_expr(confcollate, true) AS confcollate, pgc_get_expr(confctype, true) AS confctype, pgc_get_expr(confcollation, true) AS confcollation, pgc_get_expr(confdefaultvalue, true) AS confdefaultvalue FROM pg_constraint;

执行该 SQL 语句后，将返回表的所有约束信息，包括约束名、参照表、约束类型、约束条件等。