Search code examples
sqldatabasepostgresqldatabase-schemasql-insert

How to determine the auto-generated primary key used as a foreign key for another table

This is a two-part question. Attached is a diagram for a PostgreSQL database table design. There are four tables. Table Main has a one-to-many relationship with table Submain. Table Submain has a one-many relationship with table Subsub. The primary keys for all four tables are serial NOT NULL (so they auto-increment). Each table has multiple attributes that are not shown here.

PostgreSQL table design

Question 1. Multiple users will access this application and database. When a user accesses an application that uses this database, some of their information will be stored in table Main. Subsequent information (provided by the user and other results based on the user's input) will be stored in tables Submain and Subsub. My thinking is as follows:

  1. User submits information via a form.
  2. A MainId primary key will be automatically generated and some of the user information will be placed in table Main.
  3. A record will be inserted into table Submain based on the user's input (items in Main). How can I determine what the user's primary key MainId is so that I can insert it into Submain.MainId [FK] for the new record?
  4. A record will also be inserted into Subsub and that information will be based on information in table Submain. Similarly, how can I determine Submain.Submain [PK] so that I can use it as the foreign key in Subsub.Submain [FK]?

Question 2. There is a many-to-many relationship between Main and Other (I left out the associative table). However, in order to insert a record into table Other, information is required from Subsub. There will be a one-to-one mapping between Subsub and Other. Do I need to draw out that one-to-one relationship OR can table Other be populated based on a complex SELECT/JOIN statement from table Main down to table Subsub? This might be a bad question, but I think that I need to draw a one-to-one relationship and insert a foreign key SubsubId [FK] into Other instead of trying a complicated SQL statement.

Solution

  • Answer to Q1: Use data-modifying CTEs and return the generated serial value with the RETURNING clause:

    WITH ins_main AS (
   INSERT INTO main(col1)
   VALUES ('some value 1')
   RETURNING main_id    
   )
, ins_submain AS (
   INSERT INTO submain (main_id, col2)
   SELECT main_id, 'some value 2'
   FROM   ins_main
   RETURNING submain_id
   )
INSERT INTO subsub (submain_id, col3)
SELECT submain_id, 'some value 3'
FROM   ins_submain;

    Requires Postgres 9.1 or later.
    Related answers with explanation and links: