CODEWITHSUNDEEP
sqloracle-databasesequence
Alan
Alan

Reputation: 9481

Oracle - Sequence dependent on another column?

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?

Upvotes: 2

Views: 1935

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 2

GriffeyDog
GriffeyDog

Reputation: 8386

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.

Upvotes: 0

kevinskio
kevinskio

Reputation: 4551

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.

Upvotes: 0

Related Questions