Re: pro- foreign key propaganda?

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:n9jYj.4518$7k7.64_at_flpi150.ffdc.sbc.com...
>
> "David Cressey" <cressey73_at_verizon.net> wrote in message
> news:ZxeYj.3653$Uf1.3359_at_trndny08...
> >
> > "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> > news:PseYj.9097$nl7.5836_at_flpi146.ffdc.sbc.com...
> >>
> >> "David Cressey" <cressey73_at_verizon.net> wrote in message
> >> news:RkTXj.2139$Zy1.1952_at_trndny05...
> >> >
> >> > "jonniesavell" <jsavell_at_gmail.com> wrote in message
> >> >
> >
news:9e62cc4a-4e40-4cf5-9503-2bd77e0ddf46_at_x19g2000prg.googlegroups.com...
> >> >>
> >> >> > I think you are confusing foreign keys with declarative
referential
> >> >> > integrity. Foreign keys are a must and I'm sure your database uses
> >> >> > them.
> >> >> > What you are talking about (and I agree) is a constraint.
> >> >>
> >> >> OK. I am stupid. I thought that an instance of declarative
referential
> >> >> integrity in DDL create a foreign key between one or two tables in
the
> >> >> database.
> >> >>
> >> >
> >> > Not stupid. Just ignorant. The wonderful thing about ignorance is
> >> > that
> >> > it
> >> > can be cured. Read up on the subject, as others have suggested.
> >> >
> >> > The above argument is about definitions.
> >> >
> >> >
> >> >> What's the difference?
> >> >
> >> > If I say "select * from orders inner join order_detail
> >> > on orders.order_no = order_detail.order_no;
> >> >
> >> > then order_details.order_no must be a "foreign key" regardless of
> >> > whether
> >> > it was declared as such in DDL.
> >> >
> >> > If in the above case, order_details.order_no is for some strange
> >> > reason
> >> > NOT
> >> > a foreign_key reference to the corresponding row in orders, then you
> > are
> >> > the victim of a sadistic and perverse database designer. Or perhaps
> > just
> >> > a
> >> > supremely ignorant one.
> >> >
> >>
> >> I think you're mistaken, David.
> >
> > Go back and read my comment again.
> >
> >>A foreign key constraint is a database
> >> constraint that guarantees that whenever there is a row in a
referencing
> >> table, there is one and only one row in the table it references.
> >
> > I know that, and you should know by now that I know that.
> >
>
> I know that you know, and I know that you know that I know that you know,
> but I don't know that everyone else knows what we both know--especially
> since the above argument is about definitions :-)
>
> > A foreign key and a foreign key constraint are not the same thing.
>
> That's a new one on me. What's the difference?
>
>

order_details.order_id is a foreign key that references a given order and provides a natural join to orders.order_id (presumably the primary key of orders). It's a foreign key even if you never state that fact in the DDL. If it references any non existent primary key, the data has violated referential integrity. You can prevent violations of referential integrity either by declaring a foreign key constraint or by careful programming.

I don't trust careful programming much if I'm the programmer. And if somebody else is the programmer, I trust it even less than that.

Clear? Received on Mon May 19 2008 - 21:36:02 CEST