Using UNIQUE (col1, col2) or using a composite primary key for a relation table in SQLite/SQL?

Question

i’m working on a project where i’m using SQLite and where i have a few relation tables looking similar to this one:

CREATE TABLE tag_entry_relation
(
id INTEGER PRIMARY KEY,
tag_id INTEGER REFERENCES tag(id),
entry_id INTEGER REFERENCES entry(id)
);

I know that the two "referencing" columns will together be unique (so there will be no duplicates).

I’m considering whether to use the UNIQUE constraint or if it’s better to use a composite primary key

The reason i see for using a composite primary key is simply that it requires one column less than the other option

I don't see a reason for using a UNIQUE constraint except that i'm more used to this than using a composite primary key, so i'm worried i might be missing something

What would you recommend? Grateful for help!

PS: As far as i can tell there’s no difference between SQL and SQLite in regards to this question

Answer 1

I know two reasons for using an id column in that situation:

• if you have a strict convention to give every table in your database a single primary key named id

• if you cannot exclude the possibility tag_entry_relation might becoming referenced by another table int the future (which would lead to ugly combined two-column foreign key references)

If none of those two reasons applies, then go ahead with the composite primary key, else introduce the id column.

Answer 2

It would be better to use composite primary key as it will be combined together so that the combination is unique. It will allow you to set multiple unique columns as a constraint.