Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unique over several tables

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2008
    Posts
    277
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •