Constraint question

[sin1965]

Hi all
Say for instance I have a student table S, a project table P and StudentProjectMatch table PS.
The relationships are 1:m from both P -> PS and S -> PS and optional at the 1 ends.
How can I constrain a student from starting a new project until the one they are working on is finished. Using only table and row constraints and NOT PL/SQL etc.
Any help would be appreciated.
sin1965

 

[SantaMufasa]

Sin,

You have not disclosed/confirmed what data values exist when a student finishes a project. Could you please post that piece ot this puzzle?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sin1965]

hi
The schema is as such, scaled down to the essentials

table P
projID
endDate
PK (projID, endDate)

table S
studID PK

table PS
projID
studID FK on S
endDate
PK (projID, studID)
FK (projID, endDate) on P [on update cascade]????
UNQ (studID, endDate)

Yet Oracle doesn't have a cascading update feature.
The scenario I was thinking of is this.
Initially insert new project with endDate set to null. A student can then be assigned to this project, again with endDate set to null. If the same student was assigned another project it would fail (on the unique constaint) as both would have null date.
As soon as a project is finished the date is set and the PS table would have that date field updated and another project could be started.
Is this possible or am I missing something.
I cannot use PL/SQL or triggers etc.
Thanks again

 

[Thargy]

sin1965,

If my understanding is correct, your question is impossible to answer.

You state that you will initially set the project end date to null, and when the project is finished, the end date is entered. This will not work, as the end date is part of the primary key on the project table. By definition, PK data cannot be null, therefore this can never work.

Try to create a compound pk with a null value in it - see what happens

I think you should rephrase your problem as:-
A student can only work on one project at a time, how can I enforce this?

Answer, in the many/many table student_project, have two columns only, student_id and project_id, and put a unique constraint on these two columns.

How's that sound?

Regards

Tharg








Grinding away at things Oracular

 

[sin1965]

Hi
Yes the PK in P with endDate null will not work as you say.
But having unique constraint on PS(projID, studID) doesn't stop me putting a student on any number of projects! It is the PK anyway!

 

[Thargy]

sin1965,

now that the pk issue is sorted, how about dealing with the original issue.

You are trying to allow a student to only work on one project at a time. Therefore, your original analysis of a many to many relationship is incorrect. There is a one-to-one relationship between student and project, because, by your own requirement, a student can only work on one project at a time.

Therefore, in the student table, add an obligatory foreign key to the project table. That way, be design, a student can only work on one project at a time.

Regards

Tharg

Grinding away at things Oracular

 

[sin1965]

HI
From the original issue I never stated that a M:N relationship between any tables exists. P and S would have been a M:N but is broken by PS. This is a 1:M on the other tables.
Even having a 1:1 between tables P & S doesn't stop a student working on 2 projects at once. It only means they have to work on different projects.
Still a mystery!!