有条件的外键给多个表 [英] Conditional Foreign Key to multiple tables

问题描述

我有一个表格，其中包含两种类型的数据，无论是公司还是员工。

通过'C'或'E'&一个存储它的主键的列。

那么如何根据包含的数据给出外键呢？动态维护参照完整性。

  id | referenceid | documenttype 
 ------------------------------- 
 1 | 12 | E 
 2 | 7 | C 
  

现在id为1的行应该引用Employee表与pk 12&用id 2的行应该引用公司表与pk 7.

否则我必须为两个不同的表。
是否有任何其他的方式来完成它。

解决方案

如果你真的想这样做，你可以有两个可以列为CompanyId的一列，EmployeeId的列为外键。

但我宁愿试试看数据库模式设计。 $ b

I have a table which contains two type of data, either for Company or Employee.

Identifying that data by either 'C' or 'E' & a column storing primary key of it.

So how can I give foreign key depending on data contained & maintain referential integrity dynamically.

id | referenceid  | documenttype 
-------------------------------
1  | 12           | E 
2  | 7            | C 

Now row with id 1 should reference Employee table with pk 12 & row with id 2 should reference Company table with pk 7.

Otherwise I have to make two different tables for both. Is there any other way to accomplish it.

解决方案

If you really want to do this, you can have two nullable columns one for CompanyId and one for EmployeeId that act as foreign keys.

But I would rather you to try and review the database schema design.

这篇关于有条件的外键给多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！