Oracle：获取所有约束和列（主键与外键）

在本文中，我们将介绍如何使用Oracle数据库系统来获取所有表的约束和列，包括主键和外键。

约束

在数据库中，约束是用于保证数据完整性的一种方式。Oracle支持各种类型的约束，包括主键、唯一键、非空、检查和外键约束。

获取所有约束

要获取所有表的约束信息，我们可以使用以下SQL查询语句：

SELECT constraint_name, table_name, constraint_type
FROM all_constraints
WHERE owner = 'SCHEMA_NAME';

这个查询语句将返回所有位于特定模式（SCHEMA_NAME）下的表的约束信息，包括约束名称（constraint_name）、表名称（table_name）和约束类型（constraint_type）。

示例

假设我们有一个名为“employees”的表，其中包含一些约束，如主键和外键约束。我们可以使用以下查询来获取该表的所有约束信息：

SELECT constraint_name, table_name, constraint_type
FROM all_constraints
WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

这将返回表名为“EMPLOYEES”的所有约束及其类型。

列

在数据库中，列是表的基本组成部分。在Oracle中，可以使用多种方式获取表的列信息。

获取所有列

要获取特定表的所有列信息，我们可以使用以下SQL查询语句：

SELECT column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
ORDER BY column_id;

这个查询语句将返回表名称为“TABLE_NAME”的所有列的详细信息，包括列名（column_name）、数据类型（data_type）、数据长度（data_length）和是否可为空（nullable）。

示例

假设我们要获取一个名为“EMPLOYEES”的表的所有列信息，我们可以使用以下查询：

SELECT column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE owner = 'HR' AND table_name = 'EMPLOYEES'
ORDER BY column_id;

这将返回名为“EMPLOYEES”的表的所有列的详细信息。

主键

主键是具有唯一性约束的一列或一组列，用于唯一标识表中的每一行。在Oracle中，可以通过查询约束信息来获取主键列的信息。

获取主键信息

要获取表的主键信息，我们可以使用以下SQL查询语句：

SELECT a.constraint_name, a.table_name, b.column_name
FROM all_constraints a
JOIN all_cons_columns b ON a.constraint_name = b.constraint_name
WHERE a.owner = 'SCHEMA_NAME' AND a.constraint_type = 'P' AND a.table_name = 'TABLE_NAME';

这个查询语句将返回表名称为“TABLE_NAME”的主键信息，包括约束名称（constraint_name）和所属的列（column_name）。

示例

假设我们要获取一个名为“EMPLOYEES”的表的主键信息，我们可以使用以下查询：

SELECT a.constraint_name, a.table_name, b.column_name
FROM all_constraints a
JOIN all_cons_columns b ON a.constraint_name = b.constraint_name
WHERE a.owner = 'HR' AND a.constraint_type = 'P' AND a.table_name = 'EMPLOYEES';

这将返回名为“EMPLOYEES”的表的主键约束信息和对应的列名。

外键

外键是用来建立表与表之间关系的一种约束。在Oracle中，可以通过查询约束信息来获取外键列的信息。

获取外键信息

要获取表的外键信息，我们可以使用以下SQL查询语句：

SELECT a.constraint_name, a.table_name, a.r_owner, b.table_name AS r_table_name, a.delete_rule
FROM all_constraints a
JOIN all_constraints b ON a.r_owner = b.owner AND a.r_constraint_name = b.constraint_name
WHERE a.owner = 'SCHEMA_NAME' AND a.constraint_type = 'R' AND a.table_name = 'TABLE_NAME';

这个查询语句将返回表名称为“TABLE_NAME”的外键信息，包括约束名称（constraint_name）、所属的表（table_name）、引用表的所有者（r_owner）、引用表名称（r_table_name）和删除规则（delete_rule）。

示例

假设我们要获取一个名为“EMPLOYEES”的表的外键信息，我们可以使用以下查询：

SELECT a.constraint_name, a.table_name, a.r_owner, b.table_name AS r_table_name, a.delete_rule
FROM all_constraints a
JOIN all_constraints b ON a.r_owner = b.owner AND a.r_constraint_name = b.constraint_name
WHERE a.owner = 'HR' AND a.constraint_type = 'R' AND a.table_name = 'EMPLOYEES';

这将返回名为“EMPLOYEES”的表的外键约束信息和引用表的详细信息。