postgreSQL - 根据预定义的值插入有效数据

Question

我有两个表城市和郊区。Postgresql 代码：

CREATE TABLE city 
(
id uuid PRIMARY KEY,
name character varying NOT NULL,
CONSTRAINT city_id_key UNIQUE (id),
CONSTRAINT city_name_key UNIQUE (name)
)
CREATE TABLE suburb
(
id uuid PRIMARY KEY,
city_id uuid NOT NULL,
name character varying NOT NULL,
CONSTRAINT fk_suburb_city FOREIGN KEY (city_id) REFERENCES city (id),
CONSTRAINT suburb_id_key UNIQUE (id),
CONSTRAINT suburb_name_key UNIQUE (name)
)

我想创建一个名为地址的表，用于存储城市+郊区对。以下是地址表的 DDL：

CREATE TABLE address
(
id uuid NOT NULL,
city_name character varying NOT NULL,
suburb_name character varying NOT NULL
)

我想确保只有冗余的信息副本插入到地址中。下面是一个示例：

我想允许将所有city_name suburb_name对插入地址：

SELECT c.name AS city_name, s.name AS suburb_name
FROM city c, suburb s
WHERE c.id = s.city_id

结果：

A - B
A - C
X - Y

对于上面的数据，我想允许所有对：

A - B
A - C
X - Y

但是，如果有人想在地址中插入 A - Y 对，我希望 DBMS 引发错误/异常。

问题：

1. 检查这样的约束有意义吗？

2. 如果这是一个有效的想法，那么最好的解决方案是什么？触发器、存储过程、某种约束？

我更喜欢独立于 DBSM 的解决方案。我对建议的解决方案的基本思想更感兴趣，而不是在特定于 postgresql 的解决方案中。

反映@Yuri G的回答：当我阅读表单地址时，我不想加入任何。我想在其中存储真实值而不是 id。缓慢插入地址不是问题。快速阅读对我来说很重要。插入地址后，城市或郊区表的更改不是问题。因此无需更新地址表。我只想确保我插入地址的数据是有效的城市 - 郊区对(根据城市和郊区表)。

我的计划是上传包含大量数据的城市和郊区表，并使用它们来验证地址表中的插入。我不想允许我的用户插入地址，例如："纽约 - 法蒂玛·宾特·穆巴拉克街"，因为法蒂玛·宾特·穆巴拉克街在阿布扎比。

谢谢你的回答。

Answer 1

让客户端的软件操作 City & Suburb 的记录标识符，而不是它们的值。

在服务器端也这样做：

CREATE TABLE address
(
id uuid NOT NULL,
city_id character varying NOT NULL,
suburb_id character varying NOT NULL,
CONSTRAINT fk_city FOREIGN KEY (city_id) REFERENCES city (id),
CONSTRAINT fk_suburb FOREIGN KEY (suburb_id) REFERENCES suburb (id),
)

当然，在插入之前，您需要 2 个查找操作，基本上是按城市/郊区名称选择两个，以检索这些 ID(或拒绝操作)。

虽然这样你就可以保持数据完整性最简单、最有效的方式，但我相信。