Problems defining a primary key !! - Mailing list pgsql-general

Hi ,

i'm doing the planification of the courses in a university.

I have definned the following tables:

 

1- Table DAY representing a day (day_id,year,month ...)

2- Table PERIODE representing an hour of a day (periode_id,starting,ending,periode_number)

3- Table TEACHER representing a teacher (teacher_id,name,phone,address ...)

4- Table ROOM representing a ROOM in the university (room_id,description,name ...)

5- Table COURSE representing a course given by a teacher (course_id,name,...)

 

Now i have to planify the courses:

So i created the

following table PLANIFY(day_id,periode_id,teacher_id,room_id,course_id)

 

THE PROBLEM IS THAT I DON'T HAVE A WAY TO DEFINE A PRIMARY KEY for the table PLANIFY.

We must note that: 

1- a teacher can not give 2 courses in the same peiode on the same day, and can not exist in 2 different rooms in the same periode on the same day.

2- a room can not be occupied by 2 courses or 2 teachers in the same periode on the same day.

3- a course can not be given by 2 teachers or in 2 different rooms

 

ANY IDEAS ??

 

SHOULD I CHANGE THE STRUCTURE OF THE TABLE OR DEFINE A TRIGGER (which i don't know how to by the way).

 

thx for any help.