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?
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;
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.
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.
MAKEs. The user can add an arbitrary number of MAKES. Would it make sense to create possibly create so many sequences?