0

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!

3
  • Hi, and welcome to dba.se! Is there a maximum number of chapters? Do authors ever rearrange their works - or are these historical?
    – Vérace
    Commented Jun 29 at 19:49
  • you make the column an integer and think of a way to enter the chapter number, so you could insert chapter 23 and then 13 as your program defines the order
    – nbk
    Commented Jun 29 at 22:48
  • This question is similar to: Create unique sequence per set of rows sharing the same foreign key value. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem.
    – nbk
    Commented Jun 29 at 23:11

1 Answer 1

1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.