英文:
PostgreSQL: Insert records into other tables that have a reference from the main table
问题
在执行以下查询后,我想要实现以下效果:
INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
accound_id 和初始值会自动插入到所有具有相同结构的其他表中。
以下是我拥有的所有表格:
CREATE TABLE IF NOT EXISTS accounts(
unique_id uuid,
account_id BIGINT NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (unique_id, account_id)
);
CREATE TABLE IF NOT EXISTS currency_golden_credit (
ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
balance INT DEFAULT 0 NOT NULL,
CONSTRAINT fk_account FOREIGN KEY (ref_id)
REFERENCES accounts (account_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS currency_iron_credit (
ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
balance INT DEFAULT 0 NOT NULL,
CONSTRAINT fk_account FOREIGN KEY (ref_id)
REFERENCES accounts (account_id)
ON DELETE CASCADE
);
我没有找到任何解决方案,并尝试了以下查询,但它也被证明是不正确的:
with rows as (
INSERT INTO accounts (unique_id) VALUES
('c78f139a-7d71-429b-b55c-0d8fad481959')
RETURNING account_id
)
INSERT INTO currency_golden_credit (ref_id) SELECT account_id
INSERT INTO currency_iron_credit (ref_id) SELECT account_id
FROM rows;
英文:
I want to achieve such that after executing the query:
INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
the accound_id and initial value are automatically inserted into all other tables that have a common structure.
Here are all the tables I have:
CREATE TABLE IF NOT EXISTS accounts(
unique_id uuid,
account_id BIGINT NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (unique_id, account_id)
);
CREATE TABLE IF NOT EXISTS currency_golden_credit (
ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
balance INT DEFAULT 0 NOT NULL,
CONSTRAINT fk_account FOREIGN KEY (ref_id)
REFERENCES accounts (account_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS currency_iron_credit (
ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
balance INT DEFAULT 0 NOT NULL,
CONSTRAINT fk_account FOREIGN KEY (ref_id)
REFERENCES accounts (account_id)
ON DELETE CASCADE
);
I didn't find any solution and tried the query, but it also turned out to be incorrect:
with rows as (
INSERT INTO accounts (unique_id) VALUES
('c78f139a-7d71-429b-b55c-0d8fad481959')
RETURNING account_id
)
INSERT INTO currency_golden_credit (ref_id) SELECT account_id
INSERT INTO currency_iron_credit (ref_id) SELECT account_id
FROM rows;
答案1
得分: 0
正如 @Frank Heikens 所提到的,您需要审查您正在使用的模型。您的尝试在正确的方向上。但是,您需要修改查询以确保从主表插入生成的 account_id 用于随后插入其他表中。
WITH rows AS (
INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
RETURNING account_id
)
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM rows
INSERT INTO currency_iron_credit (ref_id) SELECT account_id FROM rows;
在此查询中,我在插入 accounts 表后使用了 RETURNING 子句来检索生成的 account_id。WITH 子句将返回的 account_id 分配给 rows CTE(公共表达式)。
然后,我使用来自 rows CTE 的 account_id 通过 SELECT 语句将后续的 INSERT 语句添加到其他表 currency_golden_credit 和 currency_iron_credit 中。
英文:
As @Frank Heikens mentioned, you need to review the model you are working with.Your attempt is on the right track. However, you need to modify the query to ensure that the account_id generated from the main table insertion is used in the subsequent inserts into the other tables.
WITH rows AS (
INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
RETURNING account_id
)
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM rows
INSERT INTO currency_iron_credit (ref_id) SELECT account_id FROM rows;
In this query below, I used the RETURNING
clause after the insertion into the accounts
table to retrieve the generated account_id
. The WITH
clause assigns the returned account_id
to the rows
CTE (Common Table Expression).
Then, I added the subsequent INSERT
statements into the other tables currency_golden_credit
and currency_iron_credit
using a SELECT
statement from the rows
CTE to obtain the account_id
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论