postgresql - 如何在 Postgres 中使用外键和关系表删除子记录

Question

我有一个表设计，其中使用关系表处理各种实体的“注释”。例如存在如下表

'notes' table having fields id and note
'knifes' table having as only field an id
'knife_notes' table having knife_id and note_id, being foreign keys to 'id' in knifes table 
and notes 'id' in notes table respectively.

更新:knife_notes 表中的 note_id 字段是唯一的，因此每个笔记只能与一把特定的刀相关。

当为刀(父)添加注释时，即子项时，创建注释记录并在表 knife_notes 中创建记录，从而关联注释 id 和刀 id。

两个外键具有“On Delete Cascade”。但是，当删除knife时，只会“级联”删除knife_notes中的记录，不会删除notes表中的记录。

删除刀时是否需要二次查询删除笔记记录，还是有更好的方法？

Answer 1

您所做的是在刀具和音符之间创建一种 n 对 m 的关系。您确定这是您需要的吗？

因为，按照现在设置数据模型的方式，实际上不希望删除注释。当前模型允许(除其他外):

一个。一把有 1 个音符的刀

B.具有超过 1 个音符的特定刀(knife_notes 中的 2 行指向同一把刀和不同的音符)

C.与多刀相关的特定注释

由于方案 C，数据库不能仅从 knive_notes 表级联到 notes 表:可能还有其他表依赖于特定的 note。

为了使其可视化，请考虑 knive_notes 表中的这种情况:

id   knife_id    note_id
--------------------------------------
1    11          101
2    11          102
3    12          103
4    13          103

从数据库的角度来看，这是完全合法的； note 103 用于 knive 12 和 13。现在如果你要删除 knive 12，数据库不能简单地删除 note 103，因为它仍然可以被其他 knive 使用(事实上它是)。

我能想到两种解决方案:1. 简化你的数据模型2.在PostgreSQL中创建触发器或规则

扩展选项:

1. 数据模型:

你为什么不创建一个只有

的数据模型

• 刀
• 注意(带外键的刀)

这样一把刀可以有多个音符，但一个音符总是与一把刀相关。但是，如果一个注释被多次使用或在多个角色中使用(即，如果您还有一个也需要注释的表“枪支”，或者如果刀表中的多行使用特定注释(“这把刀的颜色是红色")

1. 触发器或规则

使用规则，您可以重写 SQL 查询。使用触发器，您可以在对表进行操作之前或之后执行函数。查找triggers & rewrite rule

我自己最习惯使用触发器。我基本上会创建一个启动触发器功能的删除触发器。在该功能中，我将检查该注释是否未在其他任何地方使用，如果是这种情况，则对注释执行删除。

CREATE FUNCTION try_to_delete_note() RETURNS trigger AS ##
DECLARE
  usage_found int;
BEGIN
    -- Check that note is not used anywhere else
  usage_found = (select count(*) from knive_notes where note_id = OLD.note_id)
  IF (usage_found = 0) THEN
    DELETE from note where id = OLD.note_id    
  END IF;    

  RETURN NULL; -- result is ignored since this is an after trigger
END;
## LANGUAGE plpgsql;

CREATE TRIGGER knife_notes_deleted_trigger
AFTER DELETE ON knife_notes
FOR EACH ROW
EXECUTE PROCEDURE try_to_delete_note();

另见 this page在带有触发器示例的 postgresql 文档中。