如何在oracle19c中设置自动增量复合主键？

Question

我希望在Oracle 19c中创建一个具有复合主键和自动增量的表，如下所示：

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

我是怎么做到的？

Answer 1

在此表上创建触发器以实现此结果。假设表名为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;

Answer 2

为什么不创建单个列的pk00作为pk，并将其设置为用于存储目的的自动增量？也就是说，像往常一样插入pk01密钥(您需要使用它)。表中没有pk02，而是在查询时使用window函数表达式创建它:pk00(按pk01 order )作为pk02进行分区。

. column1 =‘column1 10’>.

其余的应用程序使用对表的查询，如下所示。

#######################

drop table table00;

create table table00 (
pk00 NUMBER GENERATED BY DEFAULT AS IDENTITY(START with 1 INCREMENT by 1),
pk01 number,
column1 varchar2(5),
column2 varchar2(5)
);


truncate table table00;

begin
insert into table00(pk01, column1, column2) values(1, 'abc', 'yest');
insert into table00(pk01, column1, column2) values(1, 'def', 'yest');
insert into table00(pk01, column1, column2) values(1, 'ghi', 'yest');
insert into table00(pk01, column1, column2) values(2, 'jkl', 'today');
insert into table00(pk01, column1, column2) values(2, 'mno', 'today');
insert into table00(pk01, column1, column2) values(2, 'pqr', 'today');
insert into table00(pk01, column1, column2) values(1, 'stu', 'yest');
commit;
end;
/

--select * from table00;

select
   --pk00,
   pk01,
   rank() over (partition by pk01 order by pk00) as pk02,
   column1,
   column2
from table00
order by 1,2
;



      PK01       PK02 COLUM COLUM
---------- ---------- ----- -----
         1          1 abc   yest 
         1          2 def   yest 
         1          3 ghi   yest 
         1          4 stu   yest 
         2          1 jkl   today
         2          2 mno   today
         2          3 pqr   today

7 rows selected. 

########################