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.
>
> 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.

Not quite. There is a slight but important difference: unique indexes do allow NULLs but PKs don't.

The real question is, however, what you want to do. Do you want to model semantics, i.e. that (client_id, product_id) are the key on the table and, therefore, are unique, or do you just want to put an index on the table for fast access. Indexes belong to the internal layer whereas constraints belong to the conceptual layer.

> 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) ;
Primary keys are unique constraints.

> 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?

Not in SQL, no. The relational model requires that each relation has a PK.

> 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?

The _only_ point of a unique constraint (or PK) is to enforce uniqueness. That they can be referenced by FKs is a different matter.

> 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)

Well, they are not the same. A unique indexes is typically used to enforce a PK, but there is no need to do so. Your system could also always do a table scan upon insert/update to check for duplicates.

> then this is all semantics. But a PK *is* different than a
> unique index or constraint, at least in DB2.

A PK is different from a unique index. It is not different from a unique constraint on a conceptual level. The DB2 information schema (Catalog) gives the PK a more exposed meaning. But that's all.

-- 
Knut Stolze
DB2 Information Integration Development
IBM Germany