0

This is my first post, so please excuse me for any obvious or simple questions as I am very new to programming and all my projects are a first to me. I am currently working on my first database project. A relational database using Oracle sql. I'm new on my course, so I am not sure on all the concepts yet, but working at it. I have used some modelling software to help me construct a 13 table database. I have setup all my columns and assigned primary and foreign keys to all 13 tables. What I am looking to do now is insert 10 rows of test data into each table. I have done the parent tables but am confused about the child tables. When I assign ID numbers to all the parent tables primary keys, will the child tables foreign keys be populated at the same time? I have not used sequences yet as I'm not 100% how to make them work, but instead inputted my own values like 100, 101, 102 etc. I know those values need to be in the foreign key, but wouldn't manually inserting them into many tables get confusing? Is there an easier approach to this or am I over complicating the process? I will need to use some queries later but I just want to be happy that the data is sound. Thanks for your help Rob

Rob Doyle
  • 1
  • 2

2 Answers2

0

No, the child table data won't be populated automatically-- if there is a child table, that implies that there is a 0 or 1 to m relationship between the two. One row in the parent table may have 0 rows in the child table or it may have dozens so nothing could possibly be populated automatically.

If you are manually assigning primary key values, you'd need to hard code those same values as the foreign key values when you insert data into the child tables. In the real world, you wouldn't manually insert data into many tables at once, you'd have an application that did so and that knew what keys to use based on parameters passed in or by getting the currval of the sequence used to populate the primary key after inserting into the parent table.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • ... or I'd prefer using the RETURNING clause to get the value that was used for the key. http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm – David Aldridge Apr 11 '15 at 12:25
0

Its necessary that data for foreign key should be present in parent table, but not the other way around. If you want to create test data, i suggest you use something like below query.

insert into child_table(fk_column,column1,column2....)
select pk_column,'#dummy_value1#','#dummy_value2#',..
from parent_table

if you have 10 rows in parent, this will add 10 rows in child. If you want more rows, e.g. 100 for each parent value you need to duplicate the parent data. for that use below query.

insert into child_table(fk_column,column1,column2....)
select pk_column,'#dummy_value1#','#dummy_value2#',..
from parent_table 
join (select level from dual connect by level<10)

this will add 100 child values for 10 parent values..

djanoti
  • 303
  • 2
  • 8