+ 2

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

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 ?

31st May 2021, 12:48 PM
Kuber Acharya
Kuber Acharya - avatar
3 Antworten
+ 3
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?
31st May 2021, 2:23 PM
Ore
Ore - avatar
+ 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 :)
31st May 2021, 2:48 PM
Kuber Acharya
Kuber Acharya - avatar
+ 1
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.
31st May 2021, 3:23 PM
Ore
Ore - avatar