我有以下兩個表來儲存使用者及其地址。我有一個與我接收和保存使用者數據的順序有關的問題。起初，使用者將只提供地址，然後被定向到外部服務。要打開此服務，我需要提供我的內部使用者 ID。該服務會驗證他們的電子郵件地址並將回復發送給我。

CREATE TABLE IF NOT EXISTS public."user"
(
   id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
   email character varying COLLATE pg_catalog."default" NOT NULL,
   CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
   CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public.adresses
(
   id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
   address character varying COLLATE pg_catalog."default" NOT NULL,
   "userId" integer,
   CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
   CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")
       REFERENCES public."user" (id) MATCH SIMPLE
       ON UPDATE NO ACTION
       ON DELETE NO ACTION
)

• 除了在表格中輸入電子郵件並最終同時輸入 ID 和電子郵件之外，我是否可以通過其他方式從序列中生成使用者 ID？
• 我可以將地址保存在地址表中引用尚未添加到使用者的外鍵嗎？

為了回答這個問題，我做了以下事情（下面的所有程式碼都可以在 fiddle here上找到）：

CREATE SEQUENCE user_id_seq;
CREATE SEQUENCE address_id_seq;

和表格：

CREATE TABLE user_
(
   id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
   email character varying COLLATE pg_catalog."default" NOT NULL,
   CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
   CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
);

和

CREATE TABLE address
(
   id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
   address character varying COLLATE pg_catalog."default" NOT NULL,
   user_id integer,
   CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
   CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY (user_id)
       REFERENCES user_ (id) MATCH SIMPLE
       ON UPDATE NO ACTION
       ON DELETE NO ACTION
);

我們將 PostgreSQL 的RETURNING子句與公用表表達式（CTE - 又名WTIH子句）一起使用。

一個簡單的例子：

WITH insert_user AS
(
 INSERT INTO user_ (email) VALUES ('user1@example.com')
 RETURNING id
)
SELECT *  FROM insert_user;

結果：

id  email
1   user2@example.com

因此，我們可以看到我們可以從INSERT帶有CTE.

現在，我們更進一步如下：

BEGIN TRANSACTION;
WITH insert_user AS
(
 INSERT INTO user_ (email) VALUES ('user1@example.com')
 RETURNING id
)
INSERT INTO address (address, user_id)
SELECT '1, Long Street, Somewhere', (SELECT id FROM insert_user)
COMMIT;

我們使用事務——以防萬一從第一步INSERT到第二步出現問題。

SELECT * FROM user_;
and
SELECT * FROM address;

結果：

id  email
2   user2@example.com
and
id         address            user_id
1   1, Long Street, Somewhere       2

我們看到user_id2 在address表中 - 來自 中INSERT的 user_表CTE。

由於表定義中的約束，您必須為 INSERT使用者的值！您可以刪除約束，但我認為這沒什麼意義。email``NOT NULL``user_

需要注意的幾點：

• 你使用CHARACTER VARYING- PostgreSQL 的TEXT數據類型更適合這個。
• COLLATE pg_catalog."default"是多餘的 - 如果未指定，則排序規則將是預設值。
• 您使用帶引號的標識符，即"userId"- 最好使用 PostgreSQL推薦的命名約定並將其轉換為user_id- 即 snake_case！
• 你有我見過的最奇怪的約束名稱！

CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),

和

CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")... REFERENCES...

這樣做的唯一理由是它取自其他一些給出這些可怕名稱的系統。address_pk_id擁有或類似的東西（即有意義）要好得多。一條錯誤消息指出約束“PK_bec464dd8d54c39c54fd32e2334”已被違反，不會告訴任何人太多！此外，通過電話傳輸將變得很困難——最好使用使用者可以關聯的簡單名稱。

要回答問題：

第一個問題：

• Can I generate the user ID from the sequence in some other way than entering an email in to the table and in the end enter both the ID and the email at the same time?

您可以執行以下操作：

SELECT nextval('user_id_seq');

結果：

nextval
     3

然後INSERT將其與地址一起放入地址表。但是，**有什麼意義呢？**您必須有某種方式將給定使用者與給定地址相關聯 - 您通過將SEQUENCE值包裝在事務中來做到這一點 - 首先從INSERT表中取出user_，然後使用RETURNING子句中的值創建地址。

第二個問題：

• Can I save the address in the addresses table referencing a foreign key which has not yet been added to users?

不！s的全部意義在於FOREIGN KEY，您不能在地址表中插入任何在表的欄位中沒有有效user_id返回的內容。id``user_

您可以放棄約束並進行一些可怕的程序混亂來跟踪在何時何地已經INSERT編輯和未編輯的內容 - 但是再次，**為什麼？**如果您允許，您的數據庫將完成跟踪所有這些的所有工作！

引用自：https://dba.stackexchange.com/questions/313950