Junction table in SQLite database

Question

Well, I am trying to build an application which allows to exchange messages between instructors and students. And I need to build a ContentProvider class, but I have encountered a problem how to represent a many-to-many relationship between the Instructor and Student classes, well it's more confusion than a problem.

I know that I need to create a junction table, for example:

CREATE TABLE InstructorStudent (
Student INTEGER REFERENCES Student (student_id),
Instructor INTEGER REFERENCES Instructor (instructor_id),
PRIMARY KEY (student_id, instructor_id))

But, I have a question: is it considered a bad practice if I make the primary key of the junction table as a simple integer which automatically increments itself?

Extra question

How do I retrieve the list of students for a particular instructor, and vise versa?

Are you looking for MySQL, Java, or something else? The primary key of the junction table should be the combination of `student_id` and `instructor_id`. This probably won't change just because you are using MySQL from Android. — Tim Biegeleisen, May 10 '17 at 05:18
No, I'm using SQLite. I'm sorry for confusion, it should have been SQLite tag instead of MySQL. — nullbyte, May 10 '17 at 05:24

score 2 · Accepted Answer · edited May 10 '17 at 10:20

I don't think you need an auto-incrementer for the join table. As long as the instructor id and student id are both unique you are fine.

How do I retrieve the list of students for a particular instructor, and vise versa?

Lets say your student table is this tbl_student

Here is how i would write the query.

select * from junctionStudentConstructor where InstructorStudent.Instructor = insid;

Replace insid with the instructor id.

Junction table in SQLite database

1 Answers1