How to ensure total participation for many to many relationship insertion in MySQL ?

Question

Hey! Sololearn users, all i wanna achieve is to maintain foreign key for total participation.

Lets consider simple example:

Suppose i have schema
student(sid,sname,sadd)

course(cid,cname) and

student-course(sid,cid)

were student-course is bridge table for many to many relationship. Now what i want to achieve is if we insert a record in student table it should automatically insert referential relationship in student-course table if any record consists in course table and same for insertion on course.

So it should look like
student:
1 John US
course:
1 DBMS
2 DSA
student-course
sid cid
1 1
1 2

Now if i insert a new student in student table it should maintain foreign key in student-course table with all courses as shown :(suppose new sid = 2)
student-course
sid cid
1 1
1 2
2 1
2 2

Also if i inserted a new record in course, it should maintain referential relationship with all student.
So how can i achieve this ?
Any efficient solution ?

Answer 1

It can't be done automatically. You have to write 2 different insert queries anytime you want to add a student or course.

#Addition of student
START TRANSACTION;
INSERT INTO `student` VALUES (2, 'Ali', 'NG');
INSERT INTO `student-course` SELECT 2, `cid` FROM `course`;
COMMIT;

#Addition of course
START TRANSACTION;
INSERT INTO `course` VALUES (3, 'OOP');
INSERT IGNORE INTO `student-course` SELECT `sid`, 3 FROM `course`;
COMMIT;

But I don't understand what you are trying to achieve. If all students offer all the courses, what is the need for this third table?

Answer 2

Ore It means for total participation its okay not to maintain foreign key ?
I am asking because i am implementing same concept for my college project not for student course relation . And i have to convert ER-Diagram into set of relations with proper rules thinking that it will not be approved if foreign key isn't maintained :)

Answer 3

Kuber Acharya I am not sure what maintaining foreign key mean in this context.
If you designed your tables well, there are no foreign keys except the composite primary keys in student-course.