如何将数据插入到带有外键的表中?

vptzau2j  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(320)

我有两张table:
用户: user_id(PK) | name 产品: product_id(PK) | product | user_id(FK) 它是一对多关系(一个用户可以有多个产品)。我用以下语句创建了表:

  1. String stmt_user = "create table user (user_id int not null generated by default as identity,"
  2. + "name varchar(20), "
  3. + "primary key(user_id))";
  4. String stmt_prod = "create table product (product_id int not null generated by default as identity,"
  5. + "product varchar(20), "
  6. + "constraint ads_pk primary key(product_id), "
  7. + "foreign key(user_id) references user(user_id) on delete cascade)";

当我想在一个id为2的特定用户中插入多个产品时,我能做什么?
类似这样的内容:“insert into product values(product)where user(user\u id)=2”。
我的table应该是这样的:

  1. |---------------------|------------------|---------------|
  2. | product_id | user_id | product |
  3. |---------------------|------------------|---------------|
  4. | 1 | 2 | bacon |
  5. |---------------------|------------------|---------------|
  6. | 2 | 2 | pizza |
  7. |---------------------|------------------|---------------|
  8. | 3 | 2 | beans |
  9. |---------------------|------------------|---------------|
iih3973s

iih3973s1#

啊终于从这里找到了我的答案:用外键插入到表中
我必须更新我的create product语句:

  1. String stmt_prod = "create table product (product_id int not null generated by default as identity,"
  2. + "product varchar(20), "
  3. + "constraint ads_pk"
  4. + "foreign key(user_id) references user(user_id) on delete cascade)";

现在,我的插入产品声明如下所示,效果良好:

  1. "insert into product (product, user_id) select product, u.user_id from user u where u.user_id = 2");

相关问题