Does my m2n-table need a primary key?

Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called

CHIP2DALE (or Dale2Chip) containing:

id NUMBER UNIQUE NOT NULL
chip_id NUMBER NOT NULL
dale_id NUMBER NOT NULL

and an UNIQUE INDEX CHIP2DALE_UQ ON (chip_id, dale_id), because multiple relations don't make sense.

My question is: Do I still need the id column here? Is there any good reason for having this column? My rows are ultimatly identified by the combination of chip_id and dale_id GIVEN THAT I have absolutely no other columns here but the two.

You never ever need the id column in any table. It is a surrogate key which replaces the natural key for pragmatic reasons. Often this is because a narrow, monotonic key gives better performance, or because join clauses become cumbersome with multiple natural key columns.

Some shops have an id on every table as a standard. Consistentcy is good, so that would suggest having the id in this table, too. If this table is the parent of a 1:m relationship it can help performance to have the parent keys directly in the child table without having to follow surrogate keys.

It does take room on disk. If you have a gazillion rows that may be an important performance consideration. This would suggest omitting the id.

Broadly, in my experience it is best to omit the id from intersection tables, and add it later if the model changes.

StackExchange Database Administrators Q#129796, answer score: 3