在MySQL表中，强制记录中的值是少数几个选项之一，受同一记录中不同值的影响(in MySQL table, force a value in a record to be one of few op

在MySQL表中，强制记录中的值是少数几个选项之一，受同一记录中不同值的影响(in MySQL table, force a value in a record to be one of few options, affected by different value in the same record)

我会更清楚..我有一个表'相机'具有以下结构：

camera(int) | day_of_week(int) | course_id(int) | open_time(TIME)| close_time(TIME)

以及具有当前以下结构的第二个表'students_courses'：

student_id(int) | course_id(int)

现在，student_id和course_id是FOREIGN KEY引用的'学生'和'课程'表，我想在'students_courses'中添加两个列 - open_time和close_time，但是catch是open_time必须是'相机'中存在的值使用相关的course_id，关闭时间必须是open_time的结果， 例如 ：

如果'相机'中有这两个记录：

`10132（相机）| 1（天）| 123（course_id）| 14:00:00（open_time）| 16时00分00秒（CLOSE_TIME） 10442（相机）| 1（天）| 123（course_id）| 18:00:00（open_time）| 19时三十分00秒（CLOSE_TIME）

我想用course_id =“123”向'students_courses'插入新记录，而'open_time'只能是两个中的一个=> '14：00：00'或'18：00：00'如果我选择'14：00：00'比'close_time'只能是'16：00：00'，显然如果'open_time'是18:00:00比'close_time'只能是'19：30：00'......

我如何实现这个逻辑？... thx

I'll make it clearer..I have a table 'cameras' with the following structure:

camera(int) | day_of_week(int) | course_id(int) | open_time(TIME)| close_time(TIME)

and the second table 'students_courses' with the current following structure:

student_id(int) | course_id(int)

now, student_id and course_id are FOREIGN KEY's referenced to 'students' and 'courses' tables , and i want to add two column's to 'students_courses' - open_time and close_time, but the catch is open_time has to be a value existing in 'cameras' with the relevant course_id, and close time has to be a result of the open_time so for example:

if there is this two records in 'cameras' :

`10132 (camera)| 1(day) | 123(course_id) | 14:00:00(open_time) | 16:00:00(close_time) 10442 (camera)| 1(day) | 123(course_id) | 18:00:00(open_time) | 19:30:00(close_time)

and i want to insert new record to 'students_courses' with the course_id = "123" than 'open_time' can only be one of the two => '14:00:00' OR '18:00:00' and if i choose '14:00:00' than 'close_time' can only be '16:00:00', obviously if 'open_time' is 18:00:00 than 'close_time' can only be '19:30:00'...

how am i implementing this logic?...thx

最满意答案

FOREIGN KEY (course_id,open_time,close_time) REFERENCES cameras (course_id, open_time, close_time)

虽然我不确定你为什么不想把FK变成相机的主键......

FOREIGN KEY (course_id,open_time,close_time) REFERENCES cameras (course_id, open_time, close_time)

Although I'm not sure why you don't just want to FK to a primary key on cameras ...