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
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

up vote 2 down vote accepted

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.

  • Makes sense, thank you! (Upvoted but I'll wait a while before accepting the answer) – sunyata Dec 7 at 12:59

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.