1

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

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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