使用从insert into返回的id进行外键记录插入 [英] using the ids returned from insert into, for record insertion with foreign key
问题描述
我有一张桌子
monster(id serial,name varchar,primary key(id))
我还有另一个表
ranged_monster(id_monster整数,距离整数,外键(id_monster)引用怪物)
我想插入两个远程怪物:一个叫做'archer goblin'攻击距离为 10 ,另一个称为'dragon',攻击距离为 50 。
到目前为止,我已经尝试过:
最糟糕的方式
插入Monster(name)值('archer goblin'),('dragon') ;
插入ranged_monster(distance)值(10),其中name = archer goblin;
插入ranged_monster(distance)值(50),其中name ='dragon';
这很不好,因为名称列允许重复,并且检索到的记录可能不止一个。
插入...并返回
如果表 ranged_monster
仅具有 id_monster $ c的列(外键) $ c>,那么我可以使用以下解决方案:
,the_ids为(
插入Monster(name)
值('archer goblin'),('dragon')
返回id
)
插入ranged_monster(id_monster)
select * from the_ids;
但是它不起作用,因为ranged_monster也具有距离列
。这样做,将插入没有距离
的怪物的ID。
可能的解决方案
创建一个具有距离的时态表,然后将该时态表与插入顺序地组合成...返回
的 the_ids
,然后将此合并的记录插入 ranged_monster
表中。
如何按照此处的要求合并两个表 https://stackoverflow.com / questions / 31171253 / sql-combine-two-tables ? (它被标记为重复,链接到此 JOIN和有什么区别?和UNION?,但该问题与另一个问题无关。
的s(name,distance)为(
值('archer goblin',10),('dragon',50)
),the_ids为(
插入Monster(name)
从s
中选择名称
返回id,name
)
插入ranged_monster(id_monster,距离)
选择id,从
s
内部联接
the_ids使用(名称)
I have a table
monster(id serial, name varchar, primary key(id))
and i have another table
ranged_monster(id_monster integer, distance integer, foreign key(id_monster) references monster)
I want to insert two ranged monsters: one is called 'archer goblin' with a range attack distance of 10, and the another is called 'dragon' with a range attack distance of 50. How can i do this in one instruction?
so far, i have tried this:
the worst way
insert into monster(name) values ('archer goblin'), ('dragon');
insert into ranged_monster(distance) values (10) where name='archer goblin';
insert into ranged_monster(distance) values (50) where name='dragon';
it is bad because the name column allows repetitions, and the retrieved records might be more than one... also having to write the name of the monster twice does not seems like a good habit.
insert into ... returning
if the table ranged_monster
only had the column (foreign key) of the id_monster
, then i could use this solution:
with the_ids as (
insert into monster(name)
values ('archer goblin'), ('dragon')
returning id
)
insert into ranged_monster(id_monster)
select * from the_ids;
however it does not work, because ranged_monster also has the column distance
. Doing so, will insert the ids of the monsters without distance
.
possible solutions
create a temporal table with the distances, and then combine this temporal table sequentially with the insert into ... returning
's the_ids
, and then insert this combined records into the ranged_monster
table.
How can i combine two tables as asked in here https://stackoverflow.com/questions/31171253/sql-combine-two-tables ? (it was marked as duplicated, linking to this What is the difference between JOIN and UNION? , but that question is not related to that another question.)
with s(name, distance) as (
values ('archer goblin', 10), ('dragon', 50)
), the_ids as (
insert into monster(name)
select name
from s
returning id, name
)
insert into ranged_monster (id_monster, distance)
select id, distance
from
s
inner join
the_ids using (name)
这篇关于使用从insert into返回的id进行外键记录插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!