CODEWITHSUNDEEP
postgresqlforeign-keyssql-insertauto-increment
random2137
random2137

Reputation: 138

Insert a value from a table in another table as foreign key

I have two tables, cinema and theater.

Table Cinema

Columns: id, name, is_active

Table Theater

Columns: id, 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.

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?

Upvotes: 1

Views: 1777

Answers (3)

Welisson Carlos Dias
Welisson Carlos Dias

Reputation: 35

this way works.

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;

Upvotes: -1

user330315
user330315

Reputation:

You have two options.

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());

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

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

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;

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

Upvotes: 2

Kristóf Varga
Kristóf Varga

Reputation: 168

INSERT INTO tableB
(
    columnA
)
SELECT
    columnA
FROM
    tableA
ORDER BY columnA desc
LIMIT 1

Upvotes: -1

Related Questions