Mysql vert quick question

ShoulderChip

I have a student table
have a registration table
and I have a curse table

I want students to be bale to sign up for multiple courses and each time appear on the registration table with a different registration Id this part is fine.

I want it to stop the same student registering for the same course twice however.

how do i do this

Registration_Id is currently the primary key for my reg table
can i make it so that there cant be two instaces of the same student id and course id?

samhail

quick solution would be to run a quick select statement on your table to see if there is a row that matchs the student if and course id before you insert. would be able to handle it better on the front end.

you could try to make the student id and course id both primary keys and both unique - though i am totally guessing on that one. no idea what it would do tbh

pauldiv

In the registrations table you could create a composite primary key consisting of the following fields:

registration_id
student_id (primary key)
course_id (primary key)

The student_id and course_id fields will link to the corresponding primaries from the student and course tables.

This will ensure that no student can be registered for the same course twice.

ShoulderChip

pauldiv wrote: »

In the registrations table you could create a composite primary key consisting of the following fields:

registration_id
student_id (primary key)
course_id (primary key)

The student_id and course_id fields will link to the corresponding primaries from the student and course tables.

This will ensure that no student can be registered for the same course twice.

yeah thats what I thought but phpmyadmin will not let me have more than one primary key,
I set them both to indexes but I am not sure what this did.

democrates

ShoulderChip wrote: »

yeah thats what I thought but phpmyadmin will not let me have more than one primary key,
I set them both to indexes but I am not sure what this did.

Get rid of the individual indexes on studentid and courseid.

Have a look at phpmyadmin again and use the option under "Indexes":
Create an index on [2] columns (GO)
Then you can pick studentid and courseid.

ShoulderChip

democrates wrote: »

Get rid of the individual indexes on studentid and courseid.

Have a look at phpmyadmin again and use the option under "Indexes":
Create an index on [2] columns (GO)
Then you can pick studentid and courseid.

cheers but i tried that and it still hasn't changed anything

Under indexes i know have
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 25 Edit Drop Registration Id

newkey INDEX 25 Edit Drop Student Id, Course ID

pauldiv

Here is a post asking how to create a composite primary key -
http://forums.mysql.com/read.php?125,116303,116303#msg-116303

You might get some ideaS from reading the replies.

Also, to use foreign keys in mySQL you may need to be using the InnoDb storage engine. You can specify what storage engine to use when issuing a CREATE TABLE statement.

You could also try executing a CREATE TABLE statement in SQL such as -

CREATE TABLE registrations(
reg_id MEDIUMINT NOT NULL AUTO_INCREMENT,
student_id MEDIUMINT NOT NULL,
course_id MEDIUMINT NOT NULL,
PRIMARY KEY (student_id, course_id)
)