primary key in many to many table

Question

let say i have two table, User and Language

user (Table Name)
-----------------
user_id (PK)
user_name
mobile
...
....

language (Table Name)
---------------------
lang_code (PK)
lang_name
...
....

The Question is, if i want to add relationship between user and language table(Many to many) which is a right way ?

solution 1

user_language (Table Name)
--------------------------
user_id (FK)
lang_code (FK)


or solution 2

user_language (Table Name)
--------------------------
user_id (PK)
lang_code (PK)


or solution 3

user_language (Table Name)
--------------------------
user_lang_id (PK)
user_id (FK)
lang_code (FK)

i saw many people add primary key on many to many table, but i think that not important and wasting space. so which is right ?

btw i'm using PostgreSQL

Composite primary key (user_id, lang_code), with both being foreign keys as well. — Esailija, Nov 07 '12 at 16:21
Consider the full code example of an n:m relationship I posted just yesterday in a [this related answer](http://stackoverflow.com/a/13259085/939860). That's how you normally do it. — Erwin Brandstetter, Nov 07 '12 at 18:09

score 4 · Accepted Answer · answered Nov 07 '12 at 16:22

Personally, I would go with solution 4:

user_language
---------------
user_id (FK)
lang_code (FK)

with composite PRIMARY KEY (user_id, lang_code)

I don't think adding the surrogate key (user_lang_id in solution 3) really adds any value to the schema and simply adds another column to have to worry about. The primary key is a good idea though to maintain uniqueness - without it, you could add the same user_id/lang_code combination multiple times.

score 2 · Answer 2 · answered Nov 07 '12 at 16:28

Composite primary key on (user_id,lang_code) is a general approach. You may need surrogate PK if you want to have a reference in another table to user_language, or if you store some other attributes in this table. But even in this case you normally have unique constraint on (user_id,lang_code)

primary key in many to many table

2 Answers2