Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Enforcing one-to-many relationships

Re: Enforcing one-to-many relationships

From: DA Morgan <>
Date: Sun, 08 Jul 2007 09:48:15 -0700
Message-ID: <>

Vladimir M. Zakharychev wrote:

> On Jul 8, 5:32 pm, Frank van Bortel <>
> wrote:

>> Hash: SHA1
>> Vladimir M. Zakharychev wrote:
>>> On Jul 7, 11:34 pm, "John" <>
>>> wrote:
>>>> <> wrote in message
>>>>> On Jul 7, 11:14 am, "John" <>
>>>>> 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
>>> In this case your relationship is wrong and should be reversed: with
>>> your current model there's going to be many instances of one
>>> manufacturer referencing one or more items it produces. This model
>>> forces you to duplicate manufacturers in the table of items over and
>>> over, and if you delete all items ever produced by a particular
>>> manufacturer, you will lose information on that manufacturer itself.
>>> This model obviously violates 2NF. Items depend on their
>>> manufacturers, not vice-versa.
>>> What you need is a set of manufacturers where each is listed only
>>> once, and a set of items referencing the set of manufacturers through
>>> a foreign key, where each item produced by particular manufacturer is
>>> listed only once.
>>> Regards,
>>> Vladimir M. Zakharychev
>>> N-Networks, makers of Dynamic PSP(tm)
>> No - the model is sound; relationships can and should
>> always be described from either endpoint:
>> Manufacturers -> Items:
>> each manufacturer delivers none, one or more items.
>> ** Optionality in the relation.
>> You may have a manufacturer, not delivering any items.
>> (Some modelers use: each manufacturer may deliver one or more items.
>> in that case, the "may" indicates the optionality of the relationship)
>> Item -> Manufacturers:
>> each and every item is delivered by at least one manufacturer
>> **Mandatory relation! You cannot have items, delivered by John Does.
>> You can have items, delivered by more that one manufacturer.
>> The case described here does not allow for the optionality in
>> manufacturers. Oracle indeed does not check that. It does
>> check the existence of child records (items) when trying to
>> remove a parent (manufacturer) or the presence of the parent,
>> when referring to it from the child (if a manufacturer is
>> referenced, it must exist)
>> - --
>> Regards,
>> Frank van Bortel
>> Top-posting is one way to shut me up...
>> Version: GnuPG v1.4.1 (MingW32)
>> iD8DBQFGkOdrLw8L4IAs830RApedAJ9gahUqkCwRvsB7B1gpWWI9qVhoVgCePTPu
>> ryqRUO7gbJTVwRsFOaRsMfk=
>> =FIRh
>> -----END PGP SIGNATURE-----
> What you describe is the same relationship as mine: you can't have an
> item without a manufacturer, but you can have a manufacturer without
> items. So manufacturers is parent and items is child with FK to
> manufacturers. This binds every item to single manufacturer and FK
> enforces non-optionality of the item to manufacturer relation.
> If an item can have more than one manufacturer, then it's many to many
> relationship and requires third table that will relate many
> manufacturers to many items and will have FK to both manufacturers and
> items and primary key of (manufacturer_id, item_id). Non-optionality
> of the item to manufacturer relation is enforced by the PK, which
> automatically implies NOT NULL on both columns.
> Maybe I've read OP's business case incorrectly but it appeared to me
> that he wanted items to be parent and manufacturers to be child which
> prompted my initial response.
> Regards,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)

Perhaps from a more realistic standpoint the relationship must be M:M as one manufacturer may have more than one item and an item may have more than one manufacturer.

The solution is use deferrable constraints, an intersecting entity, and perform the enforcement of the business rule with the primary key of the intersection.

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Jul 08 2007 - 11:48:15 CDT

Original text of this message