I'm working on a project where I'm using SQLite and where I have a few tables of relationships 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 "reference" columns will be together together (so there will be no duplicates).
I am considering whether to use the UNIQUE constraint or if it is better to use a composite primary key
The reason I see to use a composite primary key is simply that it requires one column less than the other option
I do not see a reason to use a UNIQUE constraint, except that I'm more used to this than using a compound primary key, so I'm worried that something may be missing
What would you recommend? Grateful for the help!
PS: As far as I can tell, there is no difference between SQL and SQLite with respect to this question