mysql - How to set a foreign key which is dependent on the relation of other two tables?

Question

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?

Answer 1

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.