Re: Enforcing one-to-many relationships

On Jul 7, 2:34 pm, "John" <gleneldon2000-oraclemi..._at_yahoo.co.uk> wrote:
> <fitzjarr..._at_cox.net> wrote in message
>
> news:1183834858.545940.161150_at_k79g2000hse.googlegroups.com...
>
>
>
>
>
> > On Jul 7, 11:14 am, "John" <gleneldon2000-oraclemi..._at_yahoo.co.uk>
> > wrote:
> >> What's the best way to enforce a one-to-many relationship? It's not a
> >> zero-to-many relationship - I need at least one entry in the child table.
>
> >> Thank you for any advice
>
> >> John
>
> >> (I don't want replies from Sybrand Bakker)
>
> > Too bad, really, as you're throwing away an excellent source of
> > information.
>
> > That being the case I'll tell you that which you don't want to hear:
>
> > You need to write your application to NOT allow unreferenced parent
> > records. Oracle won't do that for you. Yes, you can set the foreign
> > key columns to NOT NULL and require a valid entry, however that does
> > NOT force Oracle to require a referencing record in the child table; a
> > NULL record is not the same as a NULL foreign key value. You could
> > also use a trigger to populate a 'dummy' record in the child table,
> > but that can get complicated and can result in numerous 'dummy'
> > records if multiple users are allowed to use this application
> > simultaneously. Likewise with your application code, although if
> > you process this 'dummy' record on insert of the parent you'll stand
> > a better chance of success.
>
> > What, exactly, is the business case for this 'logic'? There must be
> > one.
>
> > David Fitzjarrell
>
> Thank you, that's very useful. I've had a suggestion of how a materialized
> view can be used. I'll let you know if it works without major performance
> problems - but we'll probably go with the enforcement being on the
> application side as you suggest.
>
> A very brief description of the business case is that all our "items" need
> at least one manufacturer.
>
> John- Hide quoted text -
>
> - Show quoted text -

Does every manufacturer 'need' to be associated with an item? If not then the obvious suggestion is to reverse your parent/child relationship.

David Fitzjarrell Received on Sat Jul 07 2007 - 15:42:25 CDT