Re: Enforcing one-to-many relationships

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