Create table that has a sequence column counting from 1 for every different foreign key

Question

Is there a way to define a table that has a sequence column counting from 1 for every different corresponding foreign key column?

I want a "local" serial for every foreign key - sorry for the inaccurate language, but by this I really mean:

Table: book_to_paragraph

book_id	paragraph_id	content
1001	1	"first paragraph..."
1001	2	"second paragraph..."
...	...	...
1002	1	"the next book, first paragraph..."
...	...	...

Table: book_to_info

book_id	name	author
...	...	...
1001	Book A	Adam Smith
1002	Book B	Bob C. Smith
...	...	...

Data definition language: book_to_info

CREATE TABLE book_to_info (
   book_id SERIAL PRIMARY KEY,
bookName VARCHAR(32) NOT NULL,
   authorName TEXT[],
   pubDate DATE
);

Data definition language: book_to_paragraph

CREATE TABLE book_to_paragraph (
   book_id INTEGER REFERENCES book_to_info(book_id),
paragraph_id ???, <- This is my question!!!
content TEXT,
   UNIQUE(book_id, paragraph_id)
);

Any help would be appreciated!

score 1 · Answer 1 · answered Jun 29 '24 at 22:58

A serial (or IDENTITY) column is not the right tool for your purpose. Those are meant to handle concurrent write access gracefully, and expressly not to produce a gapless sequence of row numbers, much less one per foreign key. They typically produce strictly monotonically increasing numbers in insert order, but even that isn't guaranteed. The only guarantee: unique numbers per row.

The best solution for you depends on the (undisclosed) exact definition of the use case. Concurrency? Cardinalities? Postgres version? What is immutable, what can change? Inserts/Updates/Deletes? Write patterns? Order criteria? Precise objectives?

See:

Create table that has a sequence column counting from 1 for every different foreign key

1 Answers1