Writing a table constraint

I have three tables as :

Student (snum(primary key),sname)

Enrolled (snum,tname)

Training (tname(primary key),tdate,thour,troom)

I have to put a constraint to the table Enrolled so that a student can not register courses(tname) that given at the same time.

I have tried something like this but I think it just gives the lessons with unique times:

select tname from Training T1 where tdate not in (select tdate from Training T2 where T1.tdate=T2.tdate)

Solution

It is possible to create such a constraint if you agree to have some redundancy.

This is what you do:

Create a unique constraint on (tname, tdate, thour) in Training:

ALTER TABLE Training ADD CONSTRAINT
  UQ_Training_NameDateHour UNIQUE (tname, tdate, thour);

Add two more columns to Enrolled:
```
ALTER TABLE Enrolled ADD tdate date NOT NULL;
ALTER TABLE Enrolled ADD thour int NOT NULL;
```
I am guessing the types here. They need to match the types of the corresponding columns in Training.

Have the new columns be part of a reference to Training:

ALTER TABLE Enrolled ADD CONSTRAINT
  FK_Enrolled_Training FOREIGN KEY (tname, tdate, thour)
               REFERENCES Training (tname, tdate, thour);

And if you already have a reference that points to tname alone, you can remove it.

Finally, create a unique constraint in Enrolled to ensure that tdate and thour are unique per snum:

ALTER TABLE Enrolled ADD CONSTRAINT
  UQ_Enrolled_NumDateHour UNIQUE (snum, tdate, thour);

This way you will have a formal constraint on the Enrolled table that will ensure a student cannot have trainings that start at the same time.

Naturally, when you insert rows into Enrolled, the references must consist of all three components. If that seems to you too big a price, then you will probably have to resort to using triggers, as suggested by Norbert van Nobelen.