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

>> a developer AND I are HAVING a friendly debate over whether it is necessary to put aPRIMARY KEY on a join table, AS opposed to just a unique index OR a unique constraint. <<

By definition any table has to have a key. You can use the PRIMARY KEY constraint OR a NOT NULL unique constraint. some products make assumptions about PRIMARY KEY constraints AND sql willuse it AS a DEFAULT in a REFERENCES clause

CREATE TABLE Clients -- I assume more than one (client_id INTEGER NOT PRIMARY KEY,
 .. );

CREATE TABLE Products - more than one?
(product_id INTEGER NOT NULL PRIMARY KEY,   ..);

Client_Product is a horrible name, even in an example!

CREATE TABLE Purchases
(client_id INTEGER NOT NULL

    REFERENCES Clients (client_id),
 product_id INTEGER NOT NULL

    REFERENCES Products (product_id),
 PRIMARY KEY (client_id, product_id),
 .. )

>> This seems unnecessary to me as long as there is noFOREIGN KEYin another table that references this PRIMARY KEY (no RI). I think I am just taking advantage of the fact that a PRIMARY KEY will slap a unique constraint on those columns, which is absolutely necessary. <<

Wrong. Back to the basics; all tables have to have a key. Otherwise, you have not enforced your business rules and are depending on all future users to do your job for you in their application code.

>> can't I accomplish the same thing by just creating a unique index on client_product? <<

Sure, if you do not like to write Standard SQL or portable code.
>> if I'm not interested in any help from an index, I could just create a unique constraint without a PRIMARY KEY: <<

Watch for NULLs in such keys.

>> I know that the rules say that every table must have a PRIMARY KEY, a field [sic] or group of fields [sic] 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"? <<

First of all, fields and columns are totally different concepts. Secondly, a key is abstract and an index is only one of many ways to implement it. You have logical and physical levels all crossed up.

>> If you don't have any FOREIGN KEYs that reference a PRIMARY KEY (no RI), what's the point of the PRIMARY KEY other than enforcing uniqueness? <<

Isn't that enough? It is called data integrity.

>> If all you want to do is enforce uniqueness, why not a unique index or unique constraint only? <<

Because a PRIMARY KEY is guaranteed to be NOT NULL, portable and to be the default for REFERENCES clauses. Received on Tue Feb 14 2006 - 19:28:57 CET