1

I have two models, A and B. A has many B. Originally, both A and B had an auto-incrementing primary key field called id, and B had an a_id field. Now I have found myself needing a unique sequence of numbers for each B within an A. I was keeping track of this within my application, but then I thought it might make more sense to let the database take care of it. I thought I could give B a compound key where the first component is a_id and the second component auto-increments, taking into consideration the a_id. So if I insert two records with a_id 1 and one with a_id 2 then I will have something like:

a_id | other_id
   1 |        1
   1 |        2
   2 |        1

If ids with lower numbers are deleted, then the sequence should not recycle these numbers. So if (1, 2) gets deleted:

a_id | other_id
   1 |        1
   2 |        1

When the next record with a_id 1 is added, the table will look like:

a_id | other_id
   1 |        1
   2 |        1
   1 |        3

How can I do this in SQL? Are there reasons not to do something like this?

I am using in-memory H2 (testing and development) and PostgreSQL 9.3 (production).

mushroom
  • 6,201
  • 5
  • 36
  • 63
  • 2
    ***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... – marc_s Dec 27 '13 at 19:45
  • 1
    why do you need this? – T McKeown Dec 27 '13 at 19:49
  • @marc_s See updates. I am trying to be as vendor unspecific as possible though. – mushroom Dec 27 '13 at 20:01
  • Is other_id really really supposed to be an id? if rows with a lower other_id for the same a_id are being deleted, is other_id supposed to be updated or not? If the row with the maximum other_id is being deleted and then another row is getting inserted, is that row's other_id supposed to be +1? – Max Dec 27 '13 at 20:05
  • @Max other_id should not be updated if lower numbers are deleted. Thanks for pointing this out. I will update the question. – mushroom Dec 27 '13 at 20:10

1 Answers1

0

The answer to your question is that you would need a trigger to get this functionality. However, you could just create a view that uses the row_number() function:

create view v_table as
    select t.*,
           row_number() over (partition by a order by id) as seqnum
    from table t;

Where I am calling the primary key for the table id.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This only works as long as lower numbers aren't getting deleted, see comments above. If a lower number is getting deleted, row_number will also get updated which is not supposed to happen. – Max Dec 27 '13 at 20:17
  • Please see the update concerning deletions. What does `as seqnum` do? I tried searching for it but couldn't find anything. – mushroom Dec 27 '13 at 20:18
  • "as seqnum" is just assigning a name to the column. – Max Dec 27 '13 at 20:19
  • @mushroom . . . If you need for the value to persist after deletions, then I think you need to use a trigger. – Gordon Linoff Dec 27 '13 at 20:20