postgresql - 将数据插入强规范化数据库并保持完整性(Postgres)

Question

我正在尝试为电话簿开发一个简单的数据库。这是我写的:

CREATE TABLE phone
(
    phone_id SERIAL PRIMARY KEY,
    phone    CHAR(15),
    sub_id   INT,   -- subscriber id --
    cat_id   INT    -- category id --
);

CREATE TABLE category
(
    cat_id   SERIAL PRIMARY KEY,    -- category id --
    cat_name CHAR(15)       -- category name --
);

CREATE TABLE  subscriber
(
    sub_id  SERIAL PRIMARY KEY,
    name    CHAR(20),
    fname   CHAR(20),   -- first name --
    lname   CHAR(20),   -- last name --
);

CREATE TABLE address
(
    addr_id       SERIAL PRIMARY KEY,
    country       CHAR(20),
    city          CHAR(20),
    street        CHAR(20),
    house_num     INT,
    apartment_num INT
);

-- many-to-many relation --
CREATE TABLE sub_link
(
    sub_id   INT REFERENCES subscriber(sub_id),
    addr_id  INT
);

我为多对多关系创建了一个链接表，因为很少有人可以住在同一地址，而一个人可以在不同时间住在不同地点。

但我不知道如何在这样的强规范化数据库中添加数据并保持数据的完整性。

第一个改进是我在地址表上添加了唯一键，因为该表不应包含重复数据:

CREATE TABLE address
(
    addr_id       SERIAL PRIMARY KEY,
    country       CHAR(20),
    city          CHAR(20),
    street        CHAR(20),
    house_num     INT,
    apartment_num INT,
    UNIQUE (country, city, street, house_num, apartment_num)
);

现在的问题是如何将关于某人的新记录添加到数据库中。我想我应该使用下一个操作顺序:

1. 向subscriber 表中插入一条记录，因为sub_link 和phone 表必须使用新订阅者的id。

2. 向 address 表中插入一条记录，因为在向 sub_link 中添加记录之前，addr_id 必须存在。

3. 在 sub_link 表中链接来自 subscriber 和 address 的最后记录。但是在这一步我遇到了一个新问题:如何在 PostgreSQL 中有效地从步骤 1) 和 2) 中获取 sub_id 和 addr_id？

4. 然后我需要在 phone 表中插入一条记录。在第 3) 步，我不知道如何有效地从以前的查询中获取 sub_id。

我在 Postgres 中阅读了有关 WITH block 的信息，但我不知道如何在我的案例中使用它。

更新我按照 ASL 的建议做了:

-- First record --
WITH t0 AS (
    WITH t1 AS (
            INSERT INTO subscriber
            VALUES(DEFAULT, 'Twilight Sparkle', NULL, NULL)
            RETURNING sub_id
    ),
    t2 AS (
            INSERT INTO address
            VALUES(DEFAULT, 'Equestria', 'Ponyville', NULL, NULL, NULL)
            RETURNING addr_id 
    )
    INSERT INTO sub_link
    VALUES((SELECT sub_id FROM t1), (SELECT addr_id FROM t2))
)
INSERT INTO phone
VALUES (DEFAULT, '000000', (SELECT sub_id FROM t1), 1);

但是我有一个错误:包含数据修改语句的 WITH 子句必须在顶层第 2 行:WITH t1 AS (INSERT INTO subscriber VALUES(DEFAULT,

Answer 1

您可以使用带有 RETURNING 子句的 WITH block 在一个查询中完成所有操作。参见 PostgreSQL docs on INSERT .例如:

WITH t1 AS (INSERT INTO subscriber VALUES ... RETURNING sub_id),
t2 AS (INSERT INTO address VALUES ... RETURNING addr_id)
INSERT INTO sub_link VALUES ((SELECT sub_id FROM t1), (SELECT addr_id FROM t2))

请注意，这种简单的形式仅在向每个表中插入一行时才有效。

这有点偏离您的问题主题，但我建议您也考虑在电话表外键中创建 sub_id 和 cat_id 列(使用引用资料)。