CODEWITHSUNDEEP
mysql
sidarcy
sidarcy

Reputation: 3008

mysql prevent duplicate insert of more than one foreign keys

I have 3 tables:

The first is called 'users' and has 3 columns. id (PK), name and email.

The second is called 'courses' and also has 3 columns: id (PK), name and code.

The third is called 'user_courses' and is used to join the users and courses tables. It to has 3 columns: id (PK), userFK (fk to users.id) and courseFK (fk to courses.id)

A user can only apply for a course once, however i can insert user with value '1' and course with value '1' multiple times.

My question is how do i prevent having a duplicate userFK, courseFK combination in the user_courses table?

Upvotes: 0

Views: 496

Answers (1)

Arth
Arth

Reputation: 13110

Add a composite unique key on (userFK,courseFK):

ALTER TABLE `user_courses` ADD UNIQUE (`userFK`,`courseFK`);

Upvotes: 3

Related Questions