Re: Enforcement of 1 to many

Clifford Buetikofer wrote in message <3725B75C.203FE151_at_merck.com>...
>Gurus,
>
>I need to enforce a 1 ( mandatory ) to many relationship between 2
>tables. Can the database do this or should we control it at the
>application level ? We do have a FK on the child table but how do we
>enforce ALL parents to have at least 1 child ? Procedurally ?
>
>
>TIA,
>Cliff
>( I have 2 kids so I'm covered )
>
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views of
> my employer, Merck & Co., Inc. All responsibility for the
statements
> made in this Usenet posting resides solely and completely with the
> sender.

In Oracle 7, you cannot do this in a well-designed manner. You must create a parent record first, and it has to be valid in its own right. After the parent is created, there is no way to prevent the client to do a COMMIT, resulting in a parent without children.

You may verify that updates and deletes do not violate your constraint, however. It is the creation of a parent with child(s) that causes you trouble.

However, you do have some not-very-attractive options:

You can revoke all insert operations on your parent table from all users. Create a package through which all your data manipulations occur. The package caches the master record until a child record is created (through the package interface) and then post both records to the database. This prevents a lot of client applications to use your tables, namely those which expect to communicate with the database through DMLs.

You can leave the two tables unrelated - no foreign key from the child record to the parent record. In the BEFORE INSERT FOR EACH ROW trigger of the parent table, you write code to verify that "child" record(s) already exist. The consequence is that you will have to enforce the referential integrity yourself.

In Oracle 8, there is a concept called deferred constraints. I don't know Oracle 8, so unfortunately I cannot tell wether this may solve your problem.

Regards,

Roy Brokvam
roy.brokvam_at_conax.com Received on Tue Apr 27 1999 - 09:47:26 CDT