sql - 范围为用户 ID 的自动递增列

Question

我真的很挣扎如何实现将通过示例进行最佳描述的要求。尽管我对 postgres 的解决方案感兴趣，但考虑以下所有内容都用伪代码编写。

ID	id_for_user	笔记	由...制作
1	1	买牛奶	1
1	2	冬季轮胎	1
1	3	阅读 1 小时	1
2	1	干净的盘子	2
2	2	了解磁铁的工作原理	2

INSERT INTO notes VALUES (note: 'Learn icelandic', created_by: 1);

ID	id_for_user	笔记	由...制作
1	1	买牛奶	1
2	2	冬季轮胎	1
3	3	阅读 1 小时	1
4	1	干净的盘子	2
5	2	了解磁铁的工作原理	2
6	4	学习冰岛语	1

INSERT INTO notes VALUES (note: 'Are birds real?', created_by: 2);

ID	id_for_user	笔记	由...制作
1	1	买牛奶	1
2	2	冬季轮胎	1
3	3	阅读 1 小时	1
4	1	干净的盘子	2
5	2	了解磁铁的工作原理	2
6	4	学习冰岛语	1
7	3	鸟是真的吗？	2

我想实现这样的目标：

CREATE TABLE notes (
    id SERIAL,
    id_for_user INT DEFAULT nextval(created_by) -- Dynamic name for sequence so every user gets its own,
    note VARCHAR,
    created_by INT,
    PRIMARY KEY(id, id_for_user),
    CONSTRAINT fk_notes_created_by
      FOREIGN KEY(created_by) 
        REFERENCES users(created_by)
);

以便用户1看到（注意前端如何id_for_user）id

ID	笔记
1	买牛奶
2	冬季轮胎
3	阅读 1 小时
4	学习冰岛语

和用户2

ID	笔记
1	干净的盘子
2	了解磁铁的工作原理
3	鸟是真的吗？

基本上我想为每个用户设置自动递增的字段。然后我也可能会根据哪个用户提出请求来通过id_for_user填写后端来查询记录。create_by

这样的事情甚至可能吗？我有哪些选择？我真的很想在数据库级别上有这个逻辑。

https://www.db-fiddle.com/f/6eBvq4VCQPTmmR3W6fCnEm/2

标签： sqlpostgresql

Answer 1

尝试使用序列，此对象将控制 ID 的自动数字

例子：

CREATE SEQUENCE sequence_notes1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100;

  CREATE SEQUENCE sequence_notes2
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100;

CREATE TABLE notes (
  id SERIAL,
  id_for_user INT,
  note VARCHAR,
  created_by INT,
  PRIMARY KEY(id)
);


INSERT INTO notes (id_for_user, note, created_by) VALUES (nextval('sequence_notes1'),'Foo', 1);
INSERT INTO notes (id_for_user, note, created_by) VALUES (nextval('sequence_notes1'),'Moo', 1);
INSERT INTO notes (id_for_user, note, created_by) VALUES (nextval('sequence_notes2'),'Boo', 2);
INSERT INTO notes (id_for_user, note, created_by) VALUES (nextval('sequence_notes2'),'Loo', 2);