Re: Enforcing one-to-many relationships

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