Need to Prevent Table Inserts Based on Condition

[carter68]

I have, lets say two tables, one Course, and the other Student.

Table: Course
Attributes: CourseID
StudentID

Table: Student
Attributes: StudentID
Name

How do I prevent the student from being able to register for more than 10 classes. Can I specify this when I create the course table by using a constraint or something similiar? Or do I have to create a trigger?

 

[phpPete]

Something like this, I think:

INSERT into Course(StudentID) VALUES(StudentID) IF COUNT(StudentID) < 11;

 

[petersJazz]

I dont think that will work, I got a syntax error when trying.

You probably need to do some validation code before the insert. Like:

select count(*)
from course
where studentID = x;

if (x <= 10) 
  insert into course....

This can be done in a trigger where mySql supports triggers.

 

[phpPete]

Hey;

I posted your question on dbForums.com and got this as an answer:

-------------------------------------
You would have to perform a query in your application beforehand since MySQL does not support constraints.

e.g.


code:--------------------------------------------------------------------------------
LOCK TABLE course
SELECT COUNT( * )
FROM course
WHERE studentid = $someid

IF count > 9
echo &quot;no!&quot;
ELSE
INSERT
UNLOCK TABLE course