在多个表中创建多个元组 [英] Creating multiple tuples in multiple tables

问题描述

以下是我的表。

< pre $ create $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2）
）;

create table student
（ID int，
name varchar（20）not null，
dept_name varchar（20），
tot_cred numeric（10， 0），
主键（ID），
外键（dept_name）引用部门（dept_name）
）;

我试图完成的是java程序会提示用户

你在Department Table中需要多少个元组？
用户：1000.
在Department表中创建1000个元组。
$ b $

你在学生表中需要多少个元组？
User：500.
在学生表中创建500个元组。

现在我可以在部门中插入一个元组，所以说

 插入部门（'CSI'，'TownHall'，'120000'）; 
  

然后从这里开始做

 插入学生（id，name，dept_name，tot_cred）
选择'+ counts +'，'Student'+ counts +''，dept_name，'10'
来自部门
，其中dept_name ='CSI'。 
  

Counts ++在while循环中，所以没有重复的PK。

因此，我可以在学生表中创建10000个元组，但是我不能在部门表中创建多个元组，因为CSI不能被复制。

但是，如果我不在部门表中插入至少一个元组，那么我将失去外键约束。

有什么想法？

PS。我不是在这里为你们做简单的代码只是需要一个想法

Brandon

解决方案 一个主键。你可以用相同的方法增加它。

另外，请考虑使用 PreparedStatement for your SQL updates

通常，你的FK会指向另一张桌子的PK。因此，当您创建DEPT_ID作为主键时，您还需要修改您的学生模式以反映此更改

编辑：你的要求，你可以做这样的事情（虽然我不知道你的确切要求）&假设上述变化，你可以做这样的事情：

创建一个 PreparedStatement 像这样：

pre $ PreparedStatement pstmt = con.prepareStatement（INSERT INTO student（id，name，dept_id，tot_cred）values（？，？，？，？）） ;


既然知道了部门的数量，现在可以创建一个循环来插入元组插入到你的部门表中，然后使用这些信息插入到你的学生表中，对吗？（这听起来更像是一个家庭作业，所以我会留给你去考虑如何做这个部分）

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

解决方案

Instead of having dept_name as a primary key, create another column DEPT_ID and set it as a primary key. You can increment that in the same way.

Also, consider using a PreparedStatement for your SQL updates

As a rule, your FK would point to the PK of another table. So when you have created DEPT_ID as a primary key, you would also need to modify your student schema to reflect this change

Edit: Based on your requirement, you can do something like this (although I don't know your exact requirement) & assuming the aforementioned change, you can do something like this

Create a PreparedStatement like this:

PreparedStatement pstmt = con.prepareStatement(INSERT INTO student(id, name, dept_id,tot_cred) values(?,?,?,?)");

Since you know the number of departments that you've, you can now create a loop to insert tuples into your department table and then use this information to insert into your student table, right? (This sounds more like a homework, so I would leave it to you to think of how to do this part)

这篇关于在多个表中创建多个元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！