我在PostgreSQL数据库中有一组表,将它们称为employee
,corporation
和account
。 corporation
有一个要account
的外键("帐户"是一个"企业帐户",跨越一个或多个公司,而不是像用于登录系统的"用户帐户"(,employee
有一个要corporation
的复合外键和一个要account
的外键(它有两列:corporation_id
和account_id
;corporation
的外键使用两列(。
如果删除employee
的父corporation
行,我希望将其corporation_id
列的值设置为 NULL
,但account_id
列应保留其值。
这个想法是员工必须"属于"一个帐户(因此employee.account_id
NOT NULL
(,并且可以分配给该帐户中的任何(或没有(公司。 如果一个公司被移除,分配给它的任何员工都应该被取消分配(employee.corporation_id
设置为NULL
(,但他们仍然应该"属于"该帐户(employee.account_id
应该保持其价值(。
ON DELETE SET NULL
外键corporation (id, account_id)
会将corporation_id
和account_id
都设置为NULL
。
如果有人仍然感兴趣,还有第三种解决方法:
- 首先,仅为 corporation_id
FOREIGN KEY (corporation_id) REFERENCES corporation(id) ON DELETE SET NULL
创建一个外键约束。 - 其次,为复合键创建另一个外键约束,
FOREIGN KEY (corporation_id, account_id) REFERENCES corporation(id, account_id)
这次没有ON DELETE
。
请注意,约束的创建顺序很重要!由于第二个约束默认为NO ACTION
因此,只有在选中约束时仍然存在任何引用行时,才会引发错误。但是,由于我们的第一个约束将corporation_id设置为NULL
引用行不再存在并且一切正常。
似乎 postgresql 无法处理这个问题。
如果您只需要完成这项工作的东西,我可以想到 2 种解决方法:
- 带有员工和公司之间连接的附加表:
- 创建一个包含字段employee_id、corporation_id account_id的表。
- 外键 (employee_id, account_id( ref 员工和 (corporation_id, account_id( ref corporation with DELETE CASCADE
- 如果员工只需要在一个公司工作 - 在 (employee_id、account_id( 上添加唯一约束 最后,不会
- 使用员工表中的corporation_id
- 或者从联接中删除account_id,并使用触发器验证公司account_id是否与员工account_id相同
我最终在employee
(current_account_id
(中添加了一个额外的列,用于外键corporation
,带有DELETE SET NULL
,以及current_account_id = account_id
的检查约束。 只是这弄乱了级联id
变化:如果account.id
更改,它会级联到employee.account_id
(通过employee
的外键到account
(和employee.current_account_id
(通过employee
的外键到corporation
,通过corporation
的外键到account
(,并且在某些时候它们是不同的(它们显然不是原子更新的(, 并且它违反了 PostgreSQL 不允许延迟的检查约束。 因此,我将其更改为约束触发器,如果值不同,则会引发check_constraint
,并且是可延迟的(并且最初是延迟的(。