Unique over several tables

ichibann · #1 (**permalink**) 07-21-10, 09:57

I've got tables:

Lessons:
id int (PK)
dayNumber int
lessonNumber int
classTeacherSubjectId int (FK)

ClassTeacherSubject:
id int (PK)
classId int (FK)
teacherSubjectId int (FK)

TeacherSubject
id int (PK)
teacherId int (FK)
subjectId int (FK)

I want to create constraint or whatever works so for each record in [Lessons] any combination of dayNumber, lessonNumber and classId is UNIQUE as well as any combination of dayNumber, lessonNumber and teacherId, so no class or teacher has two different lessons at the samy time.
How to do that?

pootle flump · #2 (**permalink**) 07-21-10, 10:06

You can't do this with your current physical design.
You can't decompose surrogate keys back in to natural keys so you will have to use natural keys in your relationships.
Either that, or redesign your logical model, probably by dispensing with TeacherSubject and ClassTeacherSubject.

pootle flump · #3 (**permalink**) 07-21-10, 10:17

I'm going to move this to DBConcepts & Design - it better fits that.
I also have a stinking cold and a woolly head - I'm much less sure about what I posted.

futurity · #4 (**permalink**) 07-21-10, 11:00

Code:

create table class (
    teacher_id int,
    subject_id int, 
    foreign key (teacher_id, subject_id) references teacher_subject,  

    room_id references classroom,
    time_slot_id reference time_slot,

    unique(teacher_id, time_slot_id),
    unique(room_id, time_slot_id)
)

Leave the subject out of your constraint. Whether or not different subjects are being taught, neither teachers nor rooms can be double-booked.

Internet Services

Web Development

Digital Marketing

Consumer Tech