2

How do I create a sequence that is dependent on another column?

For example: I have a table CAR

MAKE  | CAR_NO | COLOR | MODEL
_____________________________
Honda   1        S      Civic
Honda   2        B      Civic
Honda   3        W      Civic
Toyota  1        S      Camry
Toyota  2        B      Camry
Mazda   1        W      3

So CAR_NO is the sequence and it is dependent on the column MAKE.

How can this be done?

Alan
  • 9,331
  • 14
  • 52
  • 97

3 Answers3

2

If you just need a sequential number, then you can generate it when you query:

select t.*, row_number() over (partition by make order by make) as seqnum
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I like this approach, but is there anyway to handle if a row gets deleted? It seems like this would repeat sequences by the number of rows deleted for that make. – Alan Jun 06 '16 at 19:24
0

A sequence is not the only way to do this. This looks like business logic to me and should be encapsulated in a table to hold the values so you can change them as required.

Add a table of CAR_NO_TYPE with fields ID, MAKE, CAR_NO

Add a trigger before insert on CAR 

select CAR_NO into v_car_no from CAR_NO_TYPE where MAKE = :new.MAKE;
:new.CAR_NO := v_car_no;

Or, a better way , have your application take care of this business logic by doing the query before the insert.

kevinskio
  • 4,431
  • 1
  • 22
  • 36
0

An Oracle Sequence cannot be dependent on a table column, or anything else for that matter.

You could:

1) Use a different sequence for each MAKE of car.

2) Write special logic in a database trigger, for instance, that attempts to populate CAR_NO based on MAKE and the highest previously used value for that MAKE. However, this will not work well in multi-user environments where users are inserting records of the same MAKE at the same time.

GriffeyDog
  • 8,186
  • 3
  • 22
  • 34
  • hmm.. Yes I would need it to be reliable in a multi-user environment. In my case, I do not have a constant number of `MAKE`s. The user can add an arbitrary number of MAKES. Would it make sense to create possibly create so many sequences? – Alan Jun 06 '16 at 19:29