CODEWITHSUNDEEP
javasqloracle-databasetestingoracle11g
ziggy
ziggy

Reputation: 15876

Auto generating test data while maintaining table relationships/constraints

Given the following two tables:

CREATE TABLE AUTHOR
   (
    author_id number,
        author_fname    varchar(20),
        author_lname    varchar(20),
        author_telno    varchar(20),
        author_address  varchar(200)
)
/

 CREATE TABLE AUTHOR_BOOKS
   (
        author_id_book  number,
        author_id   number,
        boook_name  varchar(10),
        book_publisher varchar(20),
        published_date  DATE,
        published_country_code  varchar(3)
)
/

In the above two table structure, author_book.author_id is a foreign key to author.author_id meaning that an author can publish more than one book. How can i generate test data for both tables. I want to generate the test data on both tables e.g. 1000 rows in AUTHOR and 2500 rows in AUTHOR_BOOK.

The area that i am struggling with is what would be the easiest way to link the the primary key in the AUTHOR table with while auto generating the content for AUTHOR_BOOKS?

What are the possible approaches that can be used to auto generate test data for table that have a one to many relationship? I would be particularly be interested in solutions in Java, PL/SQL or simply just pure SQL.

Thanks in advance.

Upvotes: 0

Views: 85

Answers (1)

vav
vav

Reputation: 4684

Most likely, you would use ORACLE sequences to populate your ID columns.

Then in your seeding script, you would generate something like this:

insert into author values (author_seq.nextval, other columns);
insert into author_books values (book_seq.nextval, author_seq.currval, other columns1);
insert into author_books values (book_seq.nextval, author_seq.currval, other columns2);
...
insert into author values (author_seq.nextval, other columns);
insert into author_books values (book_seq.nextval, author_seq.currval, other columns3);
insert into author_books values (book_seq.nextval, author_seq.currval, other columns4);

Upvotes: 1

Related Questions