如何跨多个表将外键引用到主键？ [英] how to refer foreign key to primary keys across multiple tables?

问题描述

我的数据库中有三张桌子：

医生（DoctorCode，名字，......）

护士（护士代码，名字，......）
会计（PersonCode，date，...）
，personCode是指DoctorCode和NurseCode，但是当我想将值插入到会计表中时，DoctorCode值和NurseCode值必须相同。

是否有一种解决问题的标准方法？

I have three tables in my database:
doctor(DoctorCode,Name,...)
nurse(NurseCode,Name,...)
accounting(PersonCode,date,...)
that personCode refers to DoctorCode and NurseCode but when I want to insert values to accounting table , the DoctorCode value and NurseCode value must be same.
is there a standard way to solve the problem?

推荐答案

问题来自于你将人分成两部分这一事实单独的表格。



所以不要单独的医生和护士 table，用一个 Person 表替换它们，并添加一个列，用于标识该人是医生还是护士。



所以这个人可能像

The problem comes from the fact that you have split the persons into two separate tables.

So instead of having separate Doctor and Nurse table, replace them with a single Person table and add a column which identifies if the person is a doctor or a nurse.

So the person could be something like

Person
------
- PersonCode
- PersonType (e.g. 1=doctor, 2=nurse)
- Name
- ...

外键参考会计中的nce现在只指向人员表。

The foreign key reference in Accounting would now point only to person table.

这篇关于如何跨多个表将外键引用到主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！