Re: pro- foreign key propaganda?

"David Cressey" <cressey73_at_verizon.net> wrote in message news:CEkYj.484$ju1.418_at_trndny06...
>
> "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?
>

As mud. If you don't state that fact in the DDL, then you are implicitly permitting an order_details.order_id without a corresponding orders.order_id.

I'm still not clear on the difference between a foreign key and a foreign key constraint. Is a foreign key a value? What is it if it isn't a constraint? Received on Tue May 20 2008 - 03:25:43 CEST