Re: pro- foreign key propaganda?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:4831cfa1$0$4060$9a566e8b_at_news.aliant.net...
> David Cressey wrote:
>
> > "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.
> >
> > A foreign key and a foreign key constraint are not the same thing.
> >
> > [snippage]
>
> Is there simlarly a difference between a primary key and a primary key
> constraint? Or a candidate key and a candidate key constraint?

Well, as you've said more than once, the concept of primary key doesn't exist at the RDM level of abstraction. so it might be a little unclear exactly what is meant by a "primary key" in the absence of a declared primary key constraint. I can make an educated guess, and it goes something like this:

We have a convention that, of all the candidate keys that might be used to reference a table, one of them is chosen for all of the references. By this convention, we wouldn't have some foreign keys that reference the employees table by employee_id, while others reference it by SSN. That convention, which exists only in documentation, and not in the DDL, might be called "declaring a primary key" in some environments.

I *don't* want to rekindle the old argument about whether it's smart or stupid to have primary keys without declaring them in the DDL, or having foreign keys without declaring them in the DDL. You and I are on the same side of the question about whether DBMS enforced referential integrity is worth the cost. I'm guessing we're probably on the same side about declaring primary keys (which, when I was learning RDM, was referred to as "entity integrity enforcement"). If there other people on the other side of that argument lurking out there, I'm just going to pass on trying to use either sweet reason or sarcasm to wake them up.

I'm not sure what a candidate key constraint would be. In my world, I guess you could use a UNIQUE constraint and one or more NOT NULL constraints to acheive the desired effect. It's not clear to me whether you want to do that in every case. Received on Mon May 19 2008 - 21:30:44 CEST