如何确定自动生成的主键用作另一个表的外键 [英] How to determine the auto-generated primary key used as a foreign key for another table

问题描述

这是一个分为两部分的问题。所附的是PostgreSQL数据库表设计图。有四个表。表 Main 与表 Submain 具有一对多关系。表 Submain 与表 Subsub 具有一对多关系。所有四个表的主键都是串行NOT NULL（因此它们会自动递增）。每个表都有此处未显示的多个属性。

<问题1.多个用户将访问此应用程序和数据库。当用户访问使用该数据库的应用程序时，他们的某些信息将存储在表 Main 中。后续信息（由用户提供并根据用户输入提供其他结果）将存储在表 Submain 和 Subsub 中。我的想法如下：

1. 用户通过表单提交信息。


2. A MainId 主键将自动生成，并且一些用户信息将放置在表 Main 中。


3. 将根据用户的输入（ Main 中的项目）将记录插入表 Submain 中。如何确定用户的主键 MainId 是什么，以便可以将其插入 Submain.MainId [FK] 新记录吗？


4. 一条记录也将插入到 Subsub 中，并且该信息将基于表<$ c中的信息$ c>子域。同样，如何确定 Submain.Submain [PK] ，以便可以将其用作 Subsub.Submain [FK] 中的外键code>？



问题2。 Main 和 Other （我省略了关联表）。但是，为了在表 Other 中插入一条记录，需要从 Subsub 中获得信息。 Subsub 与 Other 之间将存在一对一的映射。我是否需要得出一对一的关系，还是可以基于复杂的 SELECT / JOIN Other c>从表 Main 到表 Subsub 的语句？这可能是一个不好的问题，但是我认为我需要绘制一对一的关系并将外键 SubsubId [FK] 插入到 Other 而不是尝试复杂的SQL语句。

解决方案

回答第一季度：使用修改数据的CTE 并返回串行PK使用 RETURNING 子句：

 ，而ins_main AS（
 INSERT INTO main（col1）
值（'some value 1'）
返回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，'一些值3'
 FROM ins_submain; 
  

需要Postgres 9.1 或更高版本。

相关带有说明和链接的答案：

• 使用Postgres一次将数据插入3个表中


• RETURNING返回的PostgreSQL存储值

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.

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.

解决方案

Answer to Q1: Use data-modifying CTEs and return the serial PK 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:

• Insert data in 3 tables at a time using Postgres
• PostgreSQL store value returned by RETURNING

这篇关于如何确定自动生成的主键用作另一个表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！