Personally I always set the natural key with a not null and unique constraint, but create an artificial key for it as well. As an example, if we had a product table, the product_sku is defined as not null with a unique constraint on it, while product_id is the primary key which all other tables reference as a foreign key.
In the case of a many to many situation, I prefer to use a two column composite key. In the case of a many to many, i've never run into a case where I needed to reference a single row in that table without knowing about both sides of that relation. Just my $0.02 -Adam On Tue, Aug 25, 2015 at 12:15 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Aug 25, 2015 at 11:40 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> Consider: >> SELECT c.registration_no, >> c.car_make, >> p.part_no >> FROM car c >> JOIN parts p ON ( p.registration_no = c.registration_no) >> WHERE registration_no = <some_var>; >> >> versus: >> SELECT c.registration_no, >> c.car_make, >> p.part_no >> FROM car c >> JOIN parts p ON ( p.id = c.id) >> WHERE registration_no = <some_var>; >> >> Why join on id when registration_no is better? >> >> > I believe you are mistaken if you think there are absolute rules you can > cling to here. But even then I would lean toward calling primary keys an > internal implementation detail that should be under the full control of the > database in which they are directly used. Artifical "natural" keys I would > lean toward turning into, possibly unique, attributes. Inherent "natural" > > keys get some consideration for using directly. > > The issue arise more, say, in a many-to-many situation. Do you define the > PK of the linking table as a two-column composite key or do you introduce > a third, serial, field to stand in for the pair? > > David J. > >
