Re: pro- foreign key propaganda?

"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. 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. Sometimes that is too restrictive. Suppose, for example, that in addition to "orders," you also have "quotes." Assume for the sake of this argument that the heading of the details for a quote is identical to the heading for the details for an order--it's just a list of parts and/or services, right? Is it sadistic and perverse or supremely ignorant to combine into a single table the details for both quotes and orders? Suppose that you have additional columns on a quote, for example, "date_quoted," "quote_valid_through," that don't appear on an order, and additional columns on an order, for example, "date_ordered," that don't appear on a quote. Without introducing nulls, you can't stuff both quotes and orders into the same table, but you CAN stuff the detail in the same table, and in fact it may make business sense to do so since it simplifies the process of transforming a quote into an order. Received on Mon May 19 2008 - 14:34:19 CEST