Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Can FK be nullable/optional by design?

Re: Can FK be nullable/optional by design?

From: Bob Badour <>
Date: Fri, 12 Dec 2003 18:04:32 -0500
Message-ID: <>

"Tobin Harris" <> wrote in message news:brddal$26unq$
> "Bob Badour" <> wrote in message
> > "Tobes (Breath)" <> wrote in message
> > news:brck8d$1t2ru$
> > >
> > > "Joe "Nuke Me Xemu" Foster" <joe_at_bftsi0.UUCP> wrote in message
> > >
> > > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > > 'logically' be included as part of the primary key?!
> > >
> > > Well, not really! I was just throwing in another option - where if the
> > > existance of one entity is dependent on another, then you can make the
> PK
> > of
> > > that entity part of a composite key in the dependent entity. It's an
> > > alternative to just non nullable foreign keys, where the related
> column(s)
> > > become part of a primary key, rather than just a foreign key. Sorry, I
> > think
> > > I need to take my anti-waffle pill, can't seem to put a good
> > > together 8-)
> >
> > Please allow me to hang an important point off of your post. The bind
> > find yourself in above is certainly not unique to you so there is no
> to
> > take this personally.
> >
> > Your bind above demonstrates a very real pitfall of confusing knowledge
> a
> > specific tool with knowledge of fundamentals. I have seen numerous
> > fall into this specific pit throughout my career. I figure at least a
> > chance the tool you know is Erwin, and you are describing their
> > "identifying" vs. "non-identifying" relationships.


> Interestingly, I have used Erwin, but only briefly! My knowledge of this
> technique came from something tought in relational theory during my
> Basically, we were being shown how to transition from conceptual ER
> to a physical model, and this specific technique was to be used if one
> entity's existance was dependent on another. I even recall the classroom
> example!

I doubt, then, you were actually taught any relational theory. With the current state of the education, I do not find that surprising.

> Don't worry, I haven't taken this personally! However, having learnt this
> approach well before sitting down and trying to use a RDBMS, I found that
> when using any RDBMS, they seemed to support the concept of a column that
> part of a primary key, and a foreign key also. So, way back then I never
> questioned it.

The candidate keys and foreign keys within a relation are generally independent of one another and can overlap. Of course, a correspondence exists between a foreign key in a referencing relation and a candidate key in the referenced relation. I said "generally independent" above because in the case that a relation refers to itself, the foreign key and candidate key are in the same relation.

Whether some or all of a foreign key forms some or all of a candidate key has no particular importance to me.

> > I have seen people using this tool create schemas with ridiculous six
> > seven part compound primary keys and call it "normalization".
> Yeah, I've fallen into this trap once or twice (although not quite so
> > Your bind above also demonstrates the dangers of using a graphical
> in
> > place of real thought and analysis.
> >
> > I respectfully suggest you will find yourself much more effective if you
> > learn the fundamentals before the tools.


> A fair suggestion, although I thought I knew at least most of the
> fundamentals! I've always put learning this before learnign the tools.
> way, when you come to learn the tools, it os interesting to see if/how
> supported the things you want to achieve, rather than pushing buttons
> what the tool could do, and then trying to understand it!
> Just out of interest, what would you describe as the fundamentals?

Chris Date's _Introduction to Database Management Systems_ makes a good start at them. I would seem foolish to try to teach them in an email message.

One would start with "What is data?" and "What does it mean to manage data?" From there, one would move to: "What principles facilitate or guide effective data management?" And onward...

Since you apparently think one can easily enumerate them in an email, what would you describe as the fundamentals? Received on Fri Dec 12 2003 - 17:04:32 CST

Original text of this message