sql - 在 Postgresql 中插入的条件规则

Question

我的问题是关于我遇到的一个 postgresql 问题。我想修改包含类似新闻组的论坛的数据库。它的消息描述了一棵树，并存储在如下表中:

\d messages
                                         Table « public.messages »
  Column   |            Type             |                          Modifiers
-----------+-----------------------------+------------------------------------------------------------------
 idmessage | integer                     | not NULL By default, nextval('messages_idmessage_seq'::regclass)
 title     | character varying(50)       | not NULL
 datemsg   | timestamp without time zone | By default, ('now'::text)::timestamp without time zone
 author    | character varying(10)       | By default, "current_user"()
 text      | text                        | not NULL
 idgroup   | integer                     | not NULL
 msgfather | integer                     |

(我翻译了法语的输出，希望我没有添加误解)

Title 是消息标题，datemsg 是它的时间戳，作者和文本非常明确，idgroup 是找到消息的讨论组标识符，msgfather 是这条消息回复的另一条消息。我想在插入时实现的规则是防止用户使用与其父组不同的组添加对消息的答案(如果它有一个，因为新讨论从没有父组发布的新消息开始)。

现在我有这样的看法:

\d newmessage
           View « public.newmessage »
  Column   |         Type          | Modifiers
-----------+-----------------------+---------------
 idmessage | integer               |
 title     | character varying(50) |
 text      | text                  |
 idgroup   | integer               |
 msgfather | integer               |
View definition :
 SELECT messages.idmessage, messages.title, messages.text, messages.idgroup, messages.msgfather
   FROM messages;
Rules :
 ins_message AS
    ON INSERT TO newmessage DO INSTEAD  INSERT INTO messages (title, text, idgroup, msgfather)
  VALUES (new.title, new.text, new.idgroup, new.msgfather)

我无法更改 View 或表，因为人们已经在使用数据库，所以我认为我必须使用规则或触发器。

我尝试将下面的规则添加到 View 中，但没有达到预期的效果:

CREATE OR REPLACE RULE ins_message_answer AS ON INSERT TO newmessage WHERE NEW.msgfather IS NOT NULL DO INSTEAD INSERT INTO messages( title, text, idgroup, msgfather) VALUES ( NEW.title, NEW.text, (SELECT idgroup FROM messages WHERE idmessage=new.msgfather), NEW.msgfather);

即使有了这个新规则，人们仍然可以回复消息并在另一个组中设置这个答案。

我也尝试通过添加 WHERE NEW.msgfather IS NULL 来更改 ins_message 规则，但是当我尝试添加消息。

那么如何实现我想做的呢？带扳机？ (我不知道如何用 postgresql 做一个)。

Answer 1

首先为(idmessage, idgroup)创建一个唯一索引:

alter table messages add constraint messages_idmessage_idgroup_key
  unique (idmessage, idgroup);

下面的 msgfather_idgroup_fkey 需要这个约束因为外键约束需要匹配唯一索引和如果没有它，将会出现 ERROR: There is no unique constraint matching given keys for referenced table "messages"。

然后添加自引用外键:

alter table messages add constraint msgfather_idgroup_fkey
  foreign key (msgfather,idgroup) references messages(idmessage,idgroup);

---

顺带一提:

• 将NOT NULL添加到datemsg和author列；

• never use rules .