Do I need a PK on a join table?

From: aj <ronald_at_mcdonalds.com>
Date: Tue, 14 Feb 2006 09:57:10 -0500
Message-ID: <11v3rueqhm58391_at_news.supernews.com>

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.

I have a client table whose PK is surrogate key client_id.   CREATE TABLE CLIENT ( CLIENT_ID INTEGER NOT NULL ,   CONSTRAINT CLIENT_ID PRIMARY KEY ( CLIENT_ID) ) ; I have a product table whose PK is surrogate key product_id.   CREATE TABLE PRODUCT (
  PRODUCT_ID INTEGER NOT NULL ,
  CONSTRAINT PRODUCT_ID PRIMARY KEY ( PRODUCT_ID) ) ; A client can have many products, and a product can have many clients, so I have a many-to-many relationship and need a join table consisting of the PK from each of the 2 tables.   CREATE TABLE CLIENT_PRODUCT (
  CLIENT_ID INTEGER NOT NULL ,
  PRODUCT_ID INTEGER NOT NULL ) ; Being a good DBA, I add FK's in CLIENT_PRODUCT which reference the PK's in CLIENT and PRODUCT.
  ALTER TABLE CLIENT_PRODUCT ADD CONSTRAINT CC1139926647460   FOREIGN KEY (CLIENT_ID) REFERENCES OLTP.CLIENT (CLIENT_ID) ;   ALTER TABLE CLIENT_PRODUCT ADD CONSTRAINT CC1139926804960   FOREIGN KEY (PRODUCT_ID) REFERENCES OLTP.PRODUCT (PRODUCT_ID) ;

• Here's the question: ***************** Is it necessary/proper to add a formal PK to the CLIENT_PRODUCT table, like: ALTER TABLE CLIENT_PRODUCT ADD CONSTRAINT CC1139926890523 PRIMARY KEY ( CLIENT_ID, PRODUCT_ID) ; By doing this, I ensure that every combination of CLIENT_ID and PRODUCT_ID in CLIENT_PRODUCT is unique, which is good. However, this seems unnecessary to me as long as there is no FK in another table that references this PK (no RI). I think I am just taking advantage of the fact that a PK will slap a unique constraint on those columns, which is absolutely necessary.

Assuming that I do NOT have another (child) table that has the combination of CLIENT_ID and PRODUCT_ID and a corresponding FK (RI), can't I accomplish the same thing by just creating a unique index on CLIENT_PRODUCT?
  CREATE UNIQUE INDEX CLIENT_PRODUCT ON CLIENT_PRODUCT   (CLIENT_ID ASC, PRODUCT_ID ASC) ;
This will also help if I search CLIENT_PRODUCT rows by CLIENT_ID. Or, if I'm not interested in any help from an index, I could just create a unique constraint without a PK:   ALTER TABLE OLTP.CLIENT_PRODUCT ADD CONSTRAINT CC1139927370976   UNIQUE (CLIENT_ID, PRODUCT_ID) ; I know that the rules say that every table must have a PK, a field or group of fields that unambiguously identifies a specific row within a table -- but is this to be taken literally? Isn't the rule better stated as "every table must have a unique index or constraint"?? If you don't have any FK's that reference a PK (no RI), whats the point of the PK other than enforcing uniqueness? If all you want to do is enforce uniqueness, why not a unique index or unique constraint only?

If you consider PK's and unique indexes to be the same thing (and some do) then this is all semantics. But a PK *is* different than a unique index or constraint, at least in DB2.

Any thoughts appreciated.

aj Received on Tue Feb 14 2006 - 15:57:10 CET