如何设置一个依赖于其他两个表关系的外键？ [英] How to set a foreign key which is dependent on the relation of other two tables?

问题描述

我有3张桌子;教师表，科目表和事件表。
1.学科和教师之间有一对多的关系，即每个教师只能教一个学科，但许多教师可以教相同的学科。
2.教师和事件之间有多对多的关系。
$ b $ h2科目表的例子

  id（PK）|名称
 ------------------- 
 1 | php 
 ------------------- 
 2 | java 
 ------------------- 
 3 | python 
 ------------------- 
 4 | c ++ 
 -------------------- 
 5 | c＃
  

教师表示例

  id（PK）|名字| subject_id（FK to subjects.id）
 ---------------------------------- 
 1 |梅西| 2 
 ---------------------------------- 
 2 |罗纳尔多| 4 
 ---------------------------------- 
 3 | pele | 1 
 ---------------------------------- 
 4 |马拉多纳| 2 
  

事件表示例

  id（PK）|场地| subject_id（FK to teacher.id）| teacher_id（FK to subject.id）
 -------------------------------------- -------------------------------------------------- ------- 
 1 | location1 | 2 |必须是1或4 
 --------------------------------------- -------------------------------------------------- ------ 
 2 | location2 | 1 |必须是3只
 ----------------------------------------- -------------------------------------------------- ----- 
 3 | location3 | 4 |必须是2只
 ----------------------------------------- -------------------------------------------------- ----- 
 4 | location4 | 4 |必须是2只
  

如何得到外键 teacher_id 字段中的c $ c>下拉菜单，其中包含基于 subject_id 中指定值的可用选项。如果在 phpmyadmin ？

$ b $中点击 b

解决方案

我不使用phpmyadmin，但是我会从

• subject.name上的唯一约束，
  
• 对{teachers.name，teachers.subject_id}，
  
• a上的唯一约束{events.venue，events.subject_id，events.teacher_id}上的唯一约束，
  
• 对{teachers.id，teachers.subject_id}的唯一约束。 $ b

前三个与身份有关;最后一个让你使用外键约束来定位{teachers.id，teachers.subject_id}。

在一般情况下，人名不是唯一的。所以你可以有两个同名的老师教相同的主题。如何处理这种问题是依赖于应用程序的。

然后是外键

• from teachers.subject_id to subjects.id，and


• from {events.teacher_id，events.subject_id} to {teachers.id，teachers.subject_id}

如果您尝试将错误的主题插入事件教师，至少会给您一个错误。

您需要使用INNODB引擎来执行外键约束。其他引擎将解析它们，但忽略它们。

lockquote
FOREIGN KEY和REFERENCES子句由InnoDB
存储引擎支持，它实现了ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name]（...）REFERENCES ...（...）。请参见第14.6.6节InnoDB
和FOREIGN KEY约束。对于其他存储引擎，子句
被解析，但是被忽略。

I have 3 tables; teachers table, subjects table and events table. 1.There is one to many relationship between subjects and teachers, that is each teacher can teach only one subject but, many teachers can teach same subjects. 2.There is a many to many relationship between teachers and events.

Example of subjects table

id(PK)      |  name
-------------------
1           |  php
-------------------
2           |  java
-------------------
3           |  python
-------------------
4           |  c++
--------------------
5           |  c#

Example of teachers table

id(PK)      |  name      | subject_id(FK to subjects.id) 
----------------------------------
1           |  messi     |  2
----------------------------------
2           |  ronaldo   |  4
----------------------------------
3           |  pele      |  1
----------------------------------
4           |  maradona  |  2

Example of events table

id(PK)      |  venue      | subject_id (FK to teacher.id)     | teacher_id(FK to subject.id)
-----------------------------------------------------------------------------------------------
1           |  location1  |       2                           |      has to be either 1 or 4
-----------------------------------------------------------------------------------------------
2           |  location2  |       1                           |      has to be 3 only
------------------------------------------------------------------------------------------------
3           |  location3  |       4                           |      has to be 2 only
------------------------------------------------------------------------------------------------
4           |  location4  |       4                           |      has to be 2 only

How do i get a foreign key drop down menu in teacher_id field with available options based on assigned value in subject_id? if not possible atleast an error message after clicking go in data insert section in phpmyadmin?

解决方案

I don't use phpmyadmin, but I'd start with

• a unique constraint on subjects.name,
• a unique constraint on the pair {teachers.name, teachers.subject_id},
• a unique constraint on {events.venue, events.subject_id, events.teacher_id},
• a unique constraint on the pair {teachers.id, teachers.subject_id}.

The first three have to do with identity; the last one lets you target {teachers.id, teachers.subject_id} with a foreign key constraint.

In the general case, people's names aren't unique. So you could have two teachers with the same name who teach the same subject. How you might approach that kind of problem is application-dependent.

Then foreign keys

• from teachers.subject_id to subjects.id, and
• from {events.teacher_id, events.subject_id} to {teachers.id, teachers.subject_id}

That will at least give you an error if you try to insert into events a teacher with the wrong subject.

You need to use the INNODB engine to enforce foreign key constraints. Other engines will parse them, but ignore them.

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...). See Section 14.6.6, "InnoDB and FOREIGN KEY Constraints". For other storage engines, the clauses are parsed but ignored.

这篇关于如何设置一个依赖于其他两个表关系的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！