CODEWITHSUNDEEP
mysqlsqldatabase
Manixman
Manixman

Reputation: 307

keys for a db table for many to many relationship

Just got a basic database question I want to ask:

If I have two tables to form a many to many relationship, so then I include another table in between the two tables to stop this relationship by creating one to many relationships, does the fields in the new table I created have to be both primary key and foriegn key or should they jut be foriegn key?

E.G

Course Table:

CourseId (PK auto)  CourseNo  CourseName
1                   2343      ICT
2                   4030      Maths

Course_Module Table: (This is the table I am asking if should be both PK and FK):

CourseId (FK course)  ModuleId (FK Module)
1                     3
1                     2
2                     1
2                     2

Module Table:

ModuleId (PK auto)  ModuleNo  ModuleName
1                   344       Algebra
2                   223       Statistics
3                   303       Systems Stratergy

Upvotes: 0

Views: 73

Answers (3)

Christoffer Lette
Christoffer Lette

Reputation: 14846

Usually they are both. In your case CourseId and ModuleId should form a composite primary key in the Course_Module table, while still being used individually as foreign keys. The sample data you provided has unique rows in that table, which is the deciding point for me.

Upvotes: 0

Rohit
Rohit

Reputation: 583

They would have to be composite PK (i.e. both courseId and ModuleId) as well as foreign keys because you need the combination of courseId and ModuleId to be unique and you also want referential integrity

Upvotes: 1

neelsg
neelsg

Reputation: 4842

You don't have to specify a primary on every table. There may however be a performance impact, so in this case I would recommend that you do specify a primary key as both fields.

Also note that you don't have to specify the fields as foreign keys and on some engines for MySQL, you actually don't have the option of using foreign keys.

Upvotes: 0

Related Questions