1-to-many data modeling.

I�m using Oracle 10g and trying to set an 1-to-many (1�m) constraint between two tables. The �many� means 1, 2, 3, � etc., not including 0. I know how to set foreign key constraint, but not this type of 1-to-many
(1, 2, 3, �) relation. Can anyone help?

Silvio,

How would you insert a record in the parent table with this constraint?
- You can't enter a child record, because it violates the foreign key rule (the parent record does not exist).
- You can't enter a parent record, because you only allow parents with at least one child.

Regards, Jan

Is it possible to create an entry in the parent table first and then an entry in child table in the same transaction, and then commit?

I'm asking this question b/c many data modeling tools do support this
type of 1-to-many (1,2,3...M) relation.

Is it possible to create an entry in the parent table first and then an entry in child table in the same transaction, and then commit?

Yes, but your constraint would trigger before the commit.
Constraints are checked at insert/update/delete time, not at commit time.

Regards, Jan
[ May 19, 2007: Message edited by: Jan Cumps ]

You cannot enter a foreign key if there is no entry in the "parent" table corresponding.
You cannot enter in the "parent" table if you insist on there being a "child" first.
You have set yourself an impossible task.

For a very short time, after entering your "parent" table line and before entering your "child" table line, you are going to have to live with a 0..* (zero-to-many) relation.