Re: Do I need a PK on a join table?

aj wrote:

> A developer and I are having a friendly debate over whether it is
> necessary to put a PK on a join table, as opposed to just a unique
> index or a unique constraint. I'm thinking that its just semantics,
> but want more opinions. I'm on DB2 v8 on Linux if that matters.

Every relation must have at least one candidate key. There are some instances where a relation will have two or more candidate keys where there is no good argument for declaring one of them primary over the others. In such a case, a primary key is optional.

In the case you describe, only one candidate key exists, and I fail to see how declaring that key to the dbms will harm anything at the logical level. On the other hand, I do see how failing to declare the key will harm by limiting what you can later express easily.

SQL and DB2 confound the issue by mixing physical structures (indexes) with purely logical integrity constraints (uniqueness/keys), by allowing the abominable NULL, and possibly by limiting the declarable foreign key constraints to reference a single primary key per table.

The thing of primary importance is logical identity. As long as you have at least one uniqueness constraint with no nullable attributes, you are good to go with respect to logical identity whether you declare that constraint as simple uniqueness, using a unique index or as a primary key.

I see it as a flaw that DB2 has three ways to declare a candidate key each with different physical consequences and subtle logical consequences which could later punish you for your choice.

Unless you have a strong argument for not wanting any index on the candidate key, I recommend declaring a primary key. If you do not and you later need to add another relation that references this table, you will have a hassle.

If you have a strong argument for wanting no index, declare a uniqueness constraint.

However, this medium is not appropriate for describing the myriad factors I considered, and I do not know all of the requirements of your data. I leave open the slim possibility that some other factor may drive the choice for a unique index instead. Received on Tue Feb 14 2006 - 18:37:52 CET