sql - 跨多个表添加序列值

Question

我的 Postgres 数据库中有以下两个表:

CREATE TABLE User (
    Id       serial UNIQUE NOT NULL, 
    Login    varchar(80) UNIQUE NOT NULL,
PRIMARY KEY (Id,Login)
);

CREATE TABLE UserData (
    Id       serial PRIMARY KEY REFERENCES Users (Id),
    Password varchar(255) NOT NULL
);

比如说，我使用 INSERT INTO Users(Id, Login) VALUES(DEFAULT, 'John') 添加了一个新用户并且还想添加 VALUES(id, 'john1980') 在 UserData 中，其中 id 是 John 的新 ID。

我如何获得该 ID？对刚创建的东西运行查询似乎是多余的。我在整个数据库中有多个这样的情况。也许我的设计总体上存在缺陷？

(我显然不会那样存储密码。)

Answer 1

1) 修正你的设计

CREATE TABLE usr (
    usr_id serial PRIMARY KEY,
   ,login  text UNIQUE NOT NULL
);

CREATE TABLE userdata (
    usr_id   int PRIMARY KEY REFERENCES usr
   ,password text NOT NULL
);

• 开始阅读 the manual about identifiers and key words .

  • 用户 是 reserved word .切勿将其用作标识符。
  • 使用描述性标识符。 id 没用。
  • 避免使用大小写混合的标识符。

• serial 用于可单独 pk 的唯一列。无需多列 pk。

• referencing 列 userdata.usr_id 也不能是序列号。使用普通的整数。

• 我只是使用 text 而不是 varchar(n)，这是可选的。 More here.

• 您可能会考虑将两个表合并为一个...

2) 在两者中插入查询

键是 RETURNING clause可用于 INSERT、UPDATE、DELETE，以立即从当前行返回值。
最好用在 data-modifying CTE :

WITH ins1 AS (
   INSERT INTO usr(login)
   VALUES ('John')             -- just omit default columns
   RETURNING usr_id            -- return automatically generated usr_id
   )
INSERT INTO userdata (usr_id, password )
SELECT i.usr_id, 'john1980'
FROM   ins1 i;