postgresql - 有什么方法可以根据数据值创建参照完整性？

Question

下图是简化图

TABLE : EMPLOYEE (TENANT_ID is a FK)
ID   |    NAME   | TENANT_ID   
1    | John      |  1

TABLE DEPARTMENT
ID   |   NAME  | TENANT_ID
1    | Physics | 1
2    | Math    | 2


TABLE : EMPLOYEE_DEPARTMENTS (Join between employee and department)
ID | EMPLOYEE_ID   | DEPARTMENT_ID 
1  | 1             | 1

如果 EMPLOYEE 值用于 TENANT 1 而 DEPARMENT_ID 来自 TENANT 2，是否有方法无法将数据插入 EMPLOYEE_DEPARTMENTS？例如其中 employee_id=1 属于 tenant=1，department_id=2 属于 tenant=2

ID |  EMPLOYEED_ID  | DEPARTMENT_ID
2  |   1            | 2

有没有办法在应用程序或数据库级别防止此类数据插入。 PS> 没有使用触发器的余地，也不想使用触发器。

Answer 1

如果没有触发器，唯一的方法是复制租户 ID，使其出现在每个表中，并使用复合主约束或唯一约束以及复合外键。

例如如果您对 EMPLOYEE(TENANT_ID, ID) 和 DEPARTMENT(TENANT_ID, ID) 有一个 UNIQUE 约束，您可以添加一个 FOREIGN KEY (TENANT_ID, EMPLOYEE_ID) REFERENCES EMPLOYEE (TENANT_ID, ID) 和 FOREIGN KEY (TENANT_ID, DEPARTMENT_ID) REFERENCES DEPARTMENT (TENANT_ID, ID)。

这要求连接表包含 TENANT_ID。

我建议将 EMPLOYEE_DEPARTMENTS 的 PRIMARY_KEY 定义为 (TENANT_ID, DEPARTMENT_ID, EMPLOYEE_ID) 并去掉无用的代理键 EMPLOYEE_DEPARTMENTS 表上的 ID，除非您的工具包/框架/ORM 没有它就无法应对。