我会更清楚..我有一个表'相机'具有以下结构:
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 ...
更多推荐
发布评论