Only accept values based on another table ? (MySQL, PostgreSQL, Django)-CodePudding

I'm developing a School/University software and I got stucked in this situation:

Table Professor

id	name
1	Mr. Ward
2	Mr. Smith

Table Subject

id	name
1	Math
2	Physics
3	English

Table ProfessorsSubject

p_id	s_id
1	1
1	2
2	3

(Mr. Ward, Math), (Mr. Ward, Physics) and (Mr. Smith, English)

Table StudentClass:

id	name	Building
10	Tenth Grade	A
11	Eleventh Grade	A

Table A:

sclass_id	subj_id	prof_id	foo	bar
10	1	1	foo1	bar1
10	2	1	foo2	bar2
10	3	NULL	foo3	bar3
11	1	1	foo4	bar4
11	2	1	foo5	bar5
11	3	2	foo6	bar6

The pair(sclass_id, subj_id) must be UNIQUE.
prof_id might be NULL.

CREATE TABLE Professor (
    id   int PRIMARY KEY AUTO_INCREMENT,
    
    name varchar(100) NOT NULL
);

CREATE TABLE Subject (
    id   int PRIMARY KEY AUTO_INCREMENT,
    
    name varchar(100) NOT NULL
);

CREATE TABLE ProfessorsSubject (
    prof_id int,
    subj_id int,
    
    PRIMARY KEY (prof_id, subj_id),
    
    FOREIGN KEY (prof_id) REFERENCES Professor(id),
    FOREIGN KEY (subj_id) REFERENCES Subject(id)
);

CREATE TABLE StudentClass (
    id       int PRIMARY KEY AUTO_INCREMENT,
    
    name     varchar(100) NOT NULL,
    building varchar(100)
);

CREATE TABLE A (
   sclass_id  int,
   subj_id    int,
   prof_id    int,
   foo        varchar(10),
   bar        varchar(10),
   
   PRIMARY KEY (sclass_id, subj_id),

   FOREIGN KEY (sclass_id) REFERENCES StudentClass (id),
   FOREIGN KEY (subj_id )  REFERENCES ProfessorsSubject (subj_id),
   FOREIGN KEY (prof_id )  REFERENCES ProfessorsSubject (prof_id)
);

/* ==================================================================== */

INSERT INTO Professor(id, name) VALUES (1, "Mr. Ward");
INSERT INTO Professor(id, name) VALUES (2, "Mr. Smith");

INSERT INTO Subject(id, name) VALUES (1, "Math");
INSERT INTO Subject(id, name) VALUES (2, "Physics");
INSERT INTO Subject(id, name) VALUES (3, "English");

INSERT INTO ProfessorsSubject (prof_id, subj_id) VALUES (1, 1);
INSERT INTO ProfessorsSubject (prof_id, subj_id) VALUES (1, 2);
INSERT INTO ProfessorsSubject (prof_id, subj_id) VALUES (2, 3);

INSERT INTO StudentClass(id, name, building) VALUES (10, "Tenth Grade", "A");
INSERT INTO StudentClass(id, name, building) VALUES (11, "Eleventh Grade", "A");

/* ==================================================================== */

INSERT INTO A (sclass_id, subj_id, prof_id, foo, bar) VALUES (10, 1, 1, "foo1", "bar1");
INSERT INTO A (sclass_id, subj_id, prof_id, foo, bar) VALUES (10, 2, 1, "foo2", "bar2");
INSERT INTO A (sclass_id, subj_id, prof_id, foo, bar) VALUES (10, 3, 1, "foo3", "bar3");

/* ==================================================================== */

Mr. Ward shouldn't be able to teach English to Tenth Grade, because this combination is not present in table ProfessorsSubject.

To avoid inconsitences in a database level, how to make sure the pair (prof_id, subj_id) in Table A respects what is in "SubjectOfProfessor" table before insert?
1.1) Is it necessary to use Procedures? Triggers ?
1.2) Is it possible to make this just using fields proprieties and/or constraints ?
What if I wanted to do this using Django ORM in a databased level ?
Reference: migrations.RunSQL('ALTER TABLE app_event ADD CONSTRAINT chronology CHECK (start_date > end_date);

CodePudding user response：

You can use a combined foreign key in the table A as you did with the primary key in the table ProfessorsSubject. A corresponding index on the referenced table is required which actually is fulfilled by its primary key already.

FOREIGN KEY (subj_id, prof_id)  REFERENCES ProfessorsSubject (subj_id, prof_id)