CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Charfi Omar
Charfi Omar

Reputation: 50

MySQL multiple PK

I am facing a problem while designing a MySQL database. I have five tables:

  1. PROFESSOR
  2. GROUP
  3. CLASSROOM
  4. SESSION
  5. COURSE_SESSION

While COURSE_SESSION columns are other tables primary keys:

|PROFESSOR_ID|GROUP_ID|CLASSROOM_ID|SESSION_ID |
| 1          |1       |3           |1          |
| 2          |2       |4           |1          |

Knowing that table SESSION has startTime and endTime columns.

I want to make sure that when I insert twice:

  1. The same PROFESSOR_ID with the same SESSION_ID
  2. Or, the same GROUP_ID with the same SESSION_ID
  3. Or, the same CLASSROOM_ID with the same SESSION_ID

The action is restricted.

Is that possible using primary key constraints? or this design is invalid for my purpose ?

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use unique constraints (or unique indexes, which do basically the same thing):

alter table course_session add constraint unq_course_session_ps
    unique (professor_id, session_id);

alter table course_session add constraint unq_course_session_gs
    unique (group_id, session_id);

alter table course_session add constraint unq_course_session_cs
    unique (classroom_id, session_id);

You can also put the unique constraint in the table definition itself....

Upvotes: 1

Related Questions