Mysql外键引用两个列，它们是两个不同表中的主键

问题描述：

可以说有三个表称为教师，学生和消息。Mysql外键引用两个列，它们是两个不同表中的主键

教师：

----------- 
ID | name | 
----------- 
t_1| Dani | 
----------- 
t_2| Billy| 
-----------

学生：

------------- 
ID | name | 
------------- 
s_1| Luckas | 
------------- 
s_2| Oliver | 
-------------

消息：

-------------------------------- 
| ID | sender_ID | receiver_ID | 
-------------------------------- 
| 1 | s_1  | s_2   | 
-------------------------------- 
| 2 | s_1  | t_1   | 
-------------------------------- 
| 3 | s_1  | t_2   | 
-------------------------------- 
| 4 | t_1  | s_2   | 
--------------------------------

抱歉表看起来如何，我不知道该怎么做更好。

我希望只有来自教师或学生的值才能被允许存储在messages.sender_id和messages.receiver_Id中，就像在FK的情况下一样。

我需要知道它是否可行，如果是这样，怎么样？

你的意思是学生或老师可以既是发送方或接收？ – Oscar

是的，我该怎么做？ – Orih90

在这种情况下，您不能使用外键。外键引用单个表中的字段。您可以使用触发器来强制执行数据完整性 – Shadow

答

你有两种不同的选择。首先是创建一个Person表，另一个PersonType。 PersonType将包含“学生”和“教师”作为值。将PersonTypeId添加到Person并为PersonType创建一个外键，这将作为学生和教师之间的区分对象。将外键添加到来自Person的Messages表中。

第二个是创建“数据库继承”。使用学生和教师之间的共同数据创建Person表。然后，使用每个人的特定数据创建Students and Teachers表，并在两者上为Person添加一个外键。将您的外键添加到来自Person表的消息。第一个解决方案更高性能。

答

MySQL不支持声明式外键约束来强制执行此完整性规则。使用所示的表格（即不改变表格），我们可以使用触发器来执行这些类型的参照完整性规则。这将需要在所有三个表上进行BEFORE INSERT，BEFORE UPDATE和BEFORE DELETE触发器的组合。

我们没有看到任何规则会阻止同一个值（例如，k_9）出现在student和teacher表中。因此，有sender_ID的消息中的一行是k_9，我们无法确定哪一个是指的，student，teacher或两者。

如果有可能改变表定义，我们可以实现一个person表为student和teacher一个超类。一对夫妇模式来实现这一点。一种可能是添加鉴别器列。

person 
ID type  name 
--- ------- ------ 
s_1 student Lukas 
s_2 student Oliver 
t_1 teacher Dani 
t_2 teacher Billy 
k_9 student Rover

使用该表，从messages声明外键约束将很简单。

我们可以返回students表的原始设计等价和teachers表：

学生

SELECT ID, name FROM person WHERE type = 'student'

教师

SELECT ID, name FROM person WHERE type = 'teacher'

但只给三问题中显示的表格，这是不可能的o声明外键约束来执行指定的参照完整性规则。执行这些完整性规则的唯一方法是通过触发器。

BEFORE INSERT ON messages 
FOR EACH ROW 
BEGIN 
    -- if NEW.sender_ID is non-NULL, verify the value appears 
    -- as value in `ID` column of either `students` or `teachers` 
    -- if not, throw an error 

    -- if NEW.receiver_ID is non-NULL, verify the value appears 
    -- as value in `ID` column of `students` or `teachers` 
    -- if not, throw an error 

END

我们还需要为UPDATE上的消息触发，以及更新和删除学生和教师

Mysql外键引用两个列，它们是两个不同表中的主键

相关推荐