将一个表中的值插入另一个表中作为外键

问题描述：

我有两个表，和 .

Table Cinema

列: id，名称，is_active

桌剧院

列: id，cinema_id

我正在按顺序插入数据库.首先，我将插入 cinema ，然后插入 theater . cinema_id.theater 是引用 cinema.id 的外键.插入 cinema 后，我将数据插入 theater ，但是在将数据插入 cinema_id .

I'm doing insertion into the DB, in sequence. First, I'll insert into cinema and then into theater. The cinema_id.theater is a foreign key that reference cinema.id. After the insertion into cinema, I'll insert data into the theater, but I need the value from cinema's id before insert the data in cinema_id.

我当时正在考虑将 ID返还给Cinema_id ，然后保存到 theater .但是我真的不知道该怎么做.

I was thinking about RETURNING id INTO cinema_id and, then, save into theater. But I really don't know how I can possibly do something like this.

有什么想法吗?有没有更好的方法来做这样的事情?

Any thoughts? Is there any better way to do something like this?

答

您有两个选择.

第一个使用 lastval()函数，该函数返回最后生成的序列值的值:

The first one is using the lastval() function which returns the value of the last generated sequence value:

insert into cinema(name, is_active) values ('Cinema One', true); 
insert into theater(cinema_id) values (lastval());

或者，您可以将序列名称传递给 currval()函数:

Alternatively you can pass the sequence name to the currval() function:

insert into theater(cinema_id) 
values (currval(pg_get_serial_sequence('cinema', 'id')));

或者，您可以使用CTE和returning子句链接这两个语句:

Alternatively you can chain the two statements using a CTE and the returning clause:

with new_cinema as (
   insert into cinema (name, is_active)   
   values ('Cinema One', true)
   returning id
)
insert into theater (cinema_id)
select id
from new_cinema;

在这两个语句中，我都假定 theater.id 也是一个生成的值.

In both statements I assume theater.id is also a generated value.

将一个表中的值插入另一个表中作为外键

相关推荐