PostgreSQL - 基于另一张表中列的约束

Question

我有两张表，一张叫做ballots，一张叫做votes。Ballots存储了一个字符串列表，表示人们可以投票的选项：

CREATE TABLE IF NOT EXISTS Polls (
  id SERIAL PRIMARY KEY,
  options text[]
);

Votes保存了用户进行的投票（其中投票是表示他们投票选项索引的整数）：

CREATE TABLE IF NOT EXISTS Votes (
  id SERIAL PRIMARY KEY,
  poll_id integer references Polls(id),
  value integer NOT NULL ,
  cast_by integer NOT NULL
);

我希望确保每当Votes表中创建一行时，对应的Polls表中的行（指poll_id相匹配的行）'value' 的值在[0，length(options))范围内。

有没有可以实现此功能的检查或外键约束？还是需要某种触发器？如果需要触发器，那么触发器应该是什么样子的？是否存在性能问题？手动使用SELECT语句查询相应投票，在插入Votes表之前断言'value'有效，这种方法与使用触发器的性能相同吗？

- user2779450

---

类似于 https://dev59.com/X2Ag5IYBdhLWcg3wvNGF - Sujitmohanty30

不要使用数组。规范化你的表格，然后你就可以使用适当的外键。 - user330315

Answer 1

根据您的要求，您不能使用检查约束，因为它可能会引用同一表的列。

您可以参考官方手册了解相关内容。

因此，在此处应该在Votes表的BEFORE INSERT事件上使用触发器，或者您可以使用函数/过程（取决于您的PostgreSQL版本）进行插入操作，在插入之前检查值并在条件不满足时引发异常。

使用触发器：

create or replace function id_exist() returns trigger as
$$
begin
if new.value<0 or new.value>=(select array_length(options,1) from polls where id=new.poll_id) then
raise exception 'value is not in range';
end if;
return new;
end;

$$
language plpgsql

CREATE TRIGGER check_value BEFORE INSERT  ON votes
    FOR EACH ROW EXECUTE PROCEDURE id_exist();

演示

- Akhilesh Mishra

Answer 2

我建议您修改数据模型，添加一个名为 PollOptions 的表格：

CREATE TABLE IF NOT EXISTS PollOptions (
  PollOptionsId SERIAL PRIMARY KEY,  -- should use generated always as identity
  PollId INT NOT NULL, REFERENCES Polls(id),
  OptionNumber int,
  Option text,
  UNIQUE (PollId, Option)
);

那么您的 Votes 表应该有一个对 PollOptions 的外键引用。您可以使用 PollOptionId 或 (PollId, Option)。

如果正确设置数据，不需要触发器或特殊函数。

- Gordon Linoff