Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!k79g2000hse.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.misc Subject: Re: Enforcing one-to-many relationships Date: Sat, 07 Jul 2007 12:00:58 -0700 Organization: http://groups.google.com Lines: 34 Message-ID: <1183834858.545940.161150@k79g2000hse.googlegroups.com> References: <468fbbd5$0$8716$ed2619ec@ptn-nntp-reader02.plus.net> NNTP-Posting-Host: 72.192.69.176 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1183834858 6402 127.0.0.1 (7 Jul 2007 19:00:58 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sat, 7 Jul 2007 19:00:58 +0000 (UTC) In-Reply-To: <468fbbd5$0$8716$ed2619ec@ptn-nntp-reader02.plus.net> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: k79g2000hse.googlegroups.com; posting-host=72.192.69.176; posting-account=B_KC8Q0AAADcSTVy4DZ59utFaLrVLOo4 Xref: usenetserver.com comp.databases.oracle.misc:248758 X-Received-Date: Sat, 07 Jul 2007 15:00:58 EDT (text.usenetserver.com) 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