PostgreSQL 如何给视图添加主键

在本文中，我们将介绍如何给 PostgreSQL 视图添加主键。

阅读更多：PostgreSQL 教程

什么是视图？

视图是一种虚拟的表，它是由查询语句生成的结果集合。视图并不存储实际的数据，而是根据基础表的数据动态生成。

为什么在视图上使用主键？

主键是唯一标识表中每一行的标识符。它可以确保数据的完整性和唯一性。尽管在视图上添加主键没有直接的物理作用，但它可以提供一些重要的功能，比如加强数据一致性和查询性能。

如何给视图添加主键？

PostgreSQL 并没有直接提供在视图上添加主键的功能，因为视图是从基础表生成的，并且无法直接在视图上进行修改。但是，我们可以通过以下方法来实现在视图上创建主键的效果。

方法一：创建带有主键的包装表

我们可以创建一个包装表，并在其上定义主键约束。然后将视图定义为查询该包装表。

-- 创建包装表
CREATE TABLE view_table (
    key_column datatype PRIMARY KEY,
    -- 其他列定义
);

-- 在包装表上添加视图定义
CREATE VIEW view_name AS
SELECT *
FROM view_table;

在这种方法中，我们创建了一个带有主键的包装表 view_table，并在其上定义了主键约束。然后我们使用 CREATE VIEW 语句来创建视图 view_name，它查询了 view_table。

方法二：使用规则（Rule）创建可更新视图

我们可以使用规则（Rule）来创建一个可更新的视图，并在规则上定义主键。

-- 创建基础表
CREATE TABLE base_table (
    key_column datatype,
    -- 其他列定义
);

-- 创建主键约束
ALTER TABLE base_table
ADD CONSTRAINT pk_base_table PRIMARY KEY (key_column);

-- 创建带有规则的视图
CREATE VIEW view_name
AS SELECT * FROM base_table
WITH RULE view_rule AS ON INSERT TO view_name
DO INSTEAD
  INSERT INTO base_table SELECT NEW.*;

在这种方法中，我们创建了一个基础表 base_table，并在其上定义了主键约束。然后我们使用 CREATE VIEW 语句创建了一个视图 view_name，并使用 WITH RULE 子句定义了规则 view_rule，该规则将视图上的插入操作重写为在基础表上执行插入。

示例说明

让我们通过一个示例来演示如何给视图添加主键。

假设我们有两个基础表 employees 和 departments，并且我们想创建一个视图来显示每个部门的员工总数。我们可以使用以下命令来创建这些表：

-- 创建 employees 表
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER REFERENCES departments(department_id)
);

-- 创建 departments 表
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 向 employees 表插入示例数据
INSERT INTO employees (name, department_id) 
VALUES ('John Doe', 1),
       ('Jane Smith', 1),
       ('Robert Johnson', 2),
       ('Emily Wang', 2);

-- 向 departments 表插入示例数据
INSERT INTO departments (name)
VALUES ('Sales'),
       ('Engineering');

接下来，我们可以使用以下方法之一来创建视图并添加主键。

方法一：创建带有主键的包装表

-- 创建包装表
CREATE TABLE view_employees (
    department_id INTEGER PRIMARY KEY,
    employee_count INTEGER
);

-- 在包装表上添加视图定义
CREATE VIEW view_department_employee_count AS
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- 查询视图
SELECT * FROM view_department_employee_count;

在这个示例中，我们创建了一个名为 view_employees 的包装表，并在其上定义了主键约束。然后我们使用 CREATE VIEW 语句创建了一个视图 view_department_employee_count，它查询了 view_employees。最后我们可以查询视图来获取每个部门的员工总数。

方法二：使用规则（Rule）创建可更新视图

-- 创建基础表
CREATE TABLE base_employees (
    department_id INTEGER,
    employee_count INTEGER
);

-- 创建主键约束
ALTER TABLE base_employees
ADD CONSTRAINT pk_base_employees PRIMARY KEY (department_id);

-- 创建可更新视图
CREATE VIEW view_department_employee_count
AS SELECT * FROM base_employees
WITH RULE view_rule AS ON INSERT TO view_department_employee_count
DO INSTEAD
  INSERT INTO base_employees SELECT NEW.department_id, NEW.employee_count;

-- 插入数据
INSERT INTO view_department_employee_count
VALUES (1, (SELECT COUNT(*) FROM employees WHERE department_id = 1)),
       (2, (SELECT COUNT(*) FROM employees WHERE department_id = 2));

-- 查询视图
SELECT * FROM view_department_employee_count;

在这个示例中，我们创建了一个名为 base_employees 的基础表，并在其上定义了主键约束。然后我们使用 CREATE VIEW 语句创建了一个视图 view_department_employee_count，并使用 WITH RULE 子句定义了规则 view_rule。最后，我们可以插入数据到视图中，并查询视图来获取每个部门的员工总数。