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: John <>
Date: Sat, 7 Jul 2007 20:34:36 +0100
Message-ID: <468feadf$0$8715$>

<> 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 Received on Sat Jul 07 2007 - 14:34:36 CDT

Original text of this message