Creating multiple tuples in multiple tables

Programming in mysql: Creating multiple tuples in multiple tables on newest questions tagged mysql – Stack Overflow

I have a question on creating multiple tuples in multiple tables using Java.

Here are my tables.

create table department(

dept_name       varchar(20) primary key,
building        varchar(15),
budget      numeric(12,2)
);

create table student
(ID             int,
 name           varchar(20) not null,
 dept_name      varchar(20),
 tot_cred       numeric(10,0),
 primary key (ID),
 foreign key (dept_name) references department(dept_name)
);

And what I am trying to accomplish is that the java program will prompt the user with

“How many tuples would you like in the Department Table?”
User: 1000.
“1000 tuples created in the Department table.”

“How many Tuples would you like in the student table?”
User: 500.
“500 tuples created in the student table.”

Now I can insert one tuple into department so say

"Insert into department ('CSI', 'TownHall', '120000')";

Then from here I do a

 Insert into student (id, name, dept_name,tot_cred)
             select '"+counts+"', 'Student"+counts+"', dept_name, '10'
             from department
             where    dept_name='CSI'.

Counts++ is in the while loop so there isn’t duplicate PK’s.

So I can create 10000 Tuples in the student table, but I cant create more than 1 tuple in the Department table because CSI can’t be duplicated.

But if I don’t insert atleast one tuple in the department table then I lose the Foreign key constraint.

Any thoughts?

PS. I’m not here for you guys to just do code just need an idea

Brandon

source: http://stackoverflow.com/questions/11633443/creating-multiple-tuples-in-multiple-tables
Programming in mysql: programming-in-mysql