Oracle 查询表外键关联|极客笔记

Oracle 查询表外键关联

1. 引言

在数据库设计中，关系型数据库的关联是非常重要的概念。在 Oracle 数据库中，关联通过外键（Foreign Key）来实现。外键是一个表中的一列或一组列，其值与另一个表中的主键或唯一键值相对应。通过外键关联，可以创建表之间的关系，一起使用这些表可以提供更强大的数据查询和操作功能。

本文将详细介绍如何使用 SQL 语句在 Oracle 数据库中查询表之间的外键关联。

2. 表的创建

为了演示外键关联的查询操作，我们首先需要创建一些示例表，这些表之间将会存在外键关联。

2.1. 创建主表

创建一个主表 users，用于存储用户的基本信息：

CREATE TABLE users (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50) NOT NULL,
  age NUMBER(3),
  email VARCHAR2(100) UNIQUE
);

2.2. 创建从表

创建一个从表 orders，用于存储用户的订单信息：

CREATE TABLE orders (
  id NUMBER(10) PRIMARY KEY,
  user_id NUMBER(10),
  product VARCHAR2(50) NOT NULL,
  price NUMBER(8,2),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

在从表的定义中，我们加入了一个外键约束，确保 orders 表中的 user_id 列的值必须在 users 表的 id 列中存在。

3. 查询外键关联

一旦表的创建完成，我们可以使用 SQL 查询语句来检索表之间的外键关联信息。以下是几个常见的查询方法。

3.1. 查询外键信息

要查询一个表的外键约束信息，可以使用 ALL_CONSTRAINTS 视图。以下 SQL 可以查询 users 表中的外键约束信息：

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'USERS' AND CONSTRAINT_TYPE = 'R';

该查询将返回 users 表上所有的外键信息。

3.2. 查询外键相关表

要查询一个表的外键引用的相关表，可以使用 ALL_CONSTRAINTS 视图。以下 SQL 可以查询以 users 表的 id 列作为外键的所有表：

SELECT * FROM ALL_CONSTRAINTS WHERE R_CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = 'USERS');

该查询将返回所有以 users 表的 id 列作为外键的表。

3.3. 查询具体外键关联信息

要查询两个表之间具体的外键关联信息，可以使用 ALL_CONS_COLUMNS 视图。以下 SQL 可以查询 orders 表中的外键约束及关联的列信息：

SELECT C.CONSTRAINT_NAME, C.TABLE_NAME, COL.COLUMN_NAME
FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS COL
WHERE C.TABLE_NAME = 'ORDERS'
  AND C.CONSTRAINT_TYPE = 'R'
  AND C.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
  AND C.OWNER = COL.OWNER;

该查询将返回 orders 表上的外键约束及关联的列信息。

4. 示例

下面给出一个完整的示例，包括表的创建和外键关联的查询。

4.1. 创建表

执行以下 SQL 语句来创建示例的主表 users 和从表 orders：

CREATE TABLE users (
  id NUMBER(10) PRIMARY KEY,
  name VARCHAR2(50) NOT NULL,
  age NUMBER(3),
  email VARCHAR2(100) UNIQUE
);

CREATE TABLE orders (
  id NUMBER(10) PRIMARY KEY,
  user_id NUMBER(10),
  product VARCHAR2(50) NOT NULL,
  price NUMBER(8,2),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

4.2. 查询外键信息

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'USERS' AND CONSTRAINT_TYPE = 'R';

输出如下：

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	R_OWNER	R_CONSTRAINT_NAME
SCOTT	FK_USER	R	USERS	SCOTT	SYS_C007134

4.3. 查询外键相关表

SELECT * FROM ALL_CONSTRAINTS WHERE R_CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = 'USERS');

输出如下：

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	R_OWNER	R_CONSTRAINT_NAME
SCOTT	FK_USER	R	USERS	SCOTT	SYS_C007134
SCOTT	SYS_C007134	P	ORDERS	SCOTT	PRIMARY_KEY

4.4. 查询具体外键关联信息

SELECT C.CONSTRAINT_NAME, C.TABLE_NAME, COL.COLUMN_NAME
FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS COL
WHERE C.TABLE_NAME = 'ORDERS'
  AND C.CONSTRAINT_TYPE = 'R'
  AND C.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
  AND C.OWNER = COL.OWNER;

输出如下：