Skip to content
Advertisement

How to set an autoincrement composite primary key in oracle19c?

I want to create a table, with composite primary key and autoincrement, in Oracle 19c, like this:

pk01 pk02 column1
==== ==== =======
1    1    abc
1    2    def
1    3    ghi
2    1    jkl
2    2    mno
3    1    pqr
1    4    stu

How I do it?

Advertisement

Answer

Create trigger on this table to achieve this result. Assume table name is test123

create or replace trigger trg_test123 before insert on test123
for each row
declare
v_id1 number;
v_max_id2 number;
begin
v_id1 := :new.id1;
select nvl(max(id2), 0) into v_max_id2 from test123 where id1 = v_id1;
v_max_id2 := v_max_id2 +1;
:new.id2 := v_max_id2;
end;
10 People found this is helpful
Advertisement