phpmyadmin error #1062 - Duplicate entry '1' for key 'PRIMARY'

[ZeferiniX]

What I'm trying to achieve is multiple values for 1 primary key.

 

Sort of looks like this

 

table "student"

student_id = 1

 

table "course"

// list of courses stored in course_id

 

table "grades"

student_id = 1

course_id = "ComSci11"

course_grade = 3.0

 

student_id = 1

course_id = "ComSci12"

course_grade = 3.1

 

student_id = 1

course_id = "ComSci13"

course_grade = 3.2

 

===============================

 

I'm pretty sure the problem here is: only 1 course_id and course_grade is allowed per student_id, how do I achieve what I want?

 

Other info if this matters:

table "student"

student_id (Primary Key)

 

table "course"

course_id (Primary Key)

 

table "grades"

student_id (Index)

course_id (Index)

[Timopomer]

it tells you EXACLY what the issue is.

 

Duplicate entry '1' for key 'PRIMARY'

 

a primary key lets you only have 1 entry of that kind for quick lookups.

 

so if a row where a collum is 1 exists,creating another one will give you an error

[Guest]

Primary keys are unique. You need to make the student_id field in grades a foregin key with a reference to students.

 

Edit: Actually, looking at your DB structure, you need a composite primary key for the grade table. However, you will still need foreign key references to the other tables.

[ZeferiniX]

Primary keys are unique. You need to make the student_id field in grades a foregin key with a reference to students.

 

Edit: Actually, looking at your DB structure, you need a composite primary key for the grade table. However, you will still need foreign key references to the other tables.

 

Will that composite primary key help me achieve what I want to happen? Shed some light senpai.

[Guest]

Will that composite primary key help me achieve what I want to happen? Shed some light senpai.

 

Yes, because you want a many-to-many relationship, so each line of the grades table must be unique. The only way to achieve this is to make the key a composite of both the student_id and course_id.

 

It might look something like this:

CREATE TABLE grade(  student_id INT(4)      NOT NULL,  course_id  INT(5)      NOT NULL,  grade      DOUBLE(4,2) NOT NULL,  CONSTRAINT grade_pk         PRIMARY KEY (student_id, course_id),  CONSTRAINT grade_student_fk FOREIGN KEY (student_id)    REFERENCES student (id)      ON DELETE CASCADE      ON UPDATE NO ACTION,  CONSTRAINT grade_course_fk  FOREIGN KEY (course_id)    REFERENCES course (course_id)      ON DELETE CASCADE      ON UPDATE NO ACTION)ENGINE = InnoDB;

[ZeferiniX]

 

Yes, because you want a many-to-many relationship, so each line of the grades table must be unique. The only way to achieve this is to make the key a composite of both the student_id and course_id.

 

It might look something like this:

CREATE TABLE grade(  student_id INT(4)      NOT NULL,  course_id  INT(5)      NOT NULL,  grade      DOUBLE(4,2) NOT NULL,  CONSTRAINT grade_pk         PRIMARY KEY (student_id, course_id),  CONSTRAINT grade_student_fk FOREIGN KEY (student_id)    REFERENCES student (id)      ON DELETE CASCADE      ON UPDATE NO ACTION,  CONSTRAINT grade_course_fk  FOREIGN KEY (course_id)    REFERENCES course (course_id)      ON DELETE CASCADE      ON UPDATE NO ACTION)ENGINE = InnoDB;

 

Thanks, I got the idea and I got it working now