Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

1-to-many data modeling.

 
Silvio Esser
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Silvio Esser
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Campbell Ritchie
Sheriff
Posts: 51419
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic