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

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

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

String stmt_user = "create table user (user_id int not null generated by default as identity," 
                   + "name varchar(20), " 
                   + "primary key(user_id))";

String stmt_prod = "create table product (product_id int not null generated by default as identity,"
                   + "product varchar(20), "
                   + "constraint ads_pk primary key(product_id), "
                   + "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应该是这样的:

|---------------------|------------------|---------------|
|      product_id     |     user_id      |    product    |
|---------------------|------------------|---------------|
|          1          |         2        |     bacon     |
|---------------------|------------------|---------------|
|          2          |         2        |     pizza     |
|---------------------|------------------|---------------|
|          3          |         2        |     beans     |
|---------------------|------------------|---------------|
iih3973s

iih3973s1#

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

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

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

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

相关问题