CODEWITHSUNDEEP
postgresql
A Question Asker
A Question Asker

Reputation: 3311

Using the serial datatype as a foreign key

Lets say that I have two tables.

The first is: table lists, with list_id SERIAL, list_name TEXT

The second table is, trivially, a table which says if the list is public: list_id INT, is_public INT

Obviously a bit of a contrived case, but I am planning out some tables and this seems to be an issue. If I insert a new list_name into table lists, then it'll give me a new serial number...but now I will need to use that serial number in the second table. Obviously in this case, you could simply add is_public to the first table, but in the case of a linking list where you have a compound key, you'll need to know the serial value that was returned.

How do people usually handle this? Do they get the return type from the insert using whatever system they're interacting with the database with?

Upvotes: 3

Views: 1979

Answers (1)

mu is too short
mu is too short

Reputation: 434585

One approach to this sort of thing is:

INSERT into the first table, use lastval() to get the "value most recently obtained with nextval for any sequence" (in the current session), and then use that value to build your next INSERT.

There's also INSERT ... RETURNING:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number.

Using INSERT ... RETURNING id basically combines the first two steps above into one so you'd do:

  • INSERT ... RETURNING id
  • INSERT ...

where the second INSERT would use the id returned from the first INSERT.

Upvotes: 2

Related Questions