主页 / dba / 问题 / 313950
Accepted
sev
Asked: 2022-07-01 06:32:21 +0800 CST

从主键序列生成 ID 而不添加行

  • 772

我有以下两个表来存储用户及其地址。我有一个与我接收和保存用户数据的顺序有关的问题。起初，用户将只提供地址，然后被定向到外部服务。要打开此服务，我需要提供我的内部用户 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？
  • 我可以将地址保存在地址表中引用尚未添加到用户的外键吗？
postgresql sequence

1 个回答

  1. Best Answer

    为了回答这个问题，我做了以下事情（下面的所有代码都可以在 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 ('[email protected]')
  RETURNING id
)
SELECT *  FROM insert_user;

    结果：

    id  email
1   [email protected]

    因此，我们可以看到我们可以从INSERT带有CTE.

    现在，我们更进一步如下：

    BEGIN TRANSACTION;
WITH insert_user AS
(
  INSERT INTO user_ (email) VALUES ('[email protected]')
  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   [email protected]
and
id         address            user_id
1   1, Long Street, Somewhere       2

    我们看到user_id2 在address表中 - 来自 中INSERT的 user_表CTE。

    由于表定义中的约束，您必须为 INSERT用户的值！您可以删除约束，但我认为这没什么意义。emailNOT NULLuser_

    需要注意的几点：

    • 你使用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返回的内容。iduser_

    您可以放弃约束并进行一些可怕的程序混乱来跟踪在何时何地已经INSERT编辑和未编辑的内容 - 但是再次，为什么？如果您允许，您的数据库将完成跟踪所有这些的所有工作！

    • 1

相关问题