Hello.

I'm designing a database in Oracle 12.2 in Toad Data Modeler. It would get lots of inserts.

I'm using identity columns as PK (basically I create a sequence and use it as default value in the column, sequence.nextval). When I connect the PK column in parent table to the child table the new FK column comes with sequence.nextval as default value.

1. Is this the expected behavior? or should I user sequence.currval as default value?

2. Should I let the app handle the value in the FK on the child table? If so, is it OK to let sequence.nextval as default value in FK because it is ignored when a value comes in the insert statement?

3. What would be the best aproach?

Thanks in advance.

Regards,

create sequence s;
create table t1 (
  c1 int default s.nextval primary key
);

create table t2 (
  t1_c1 int 
    references t1 ( c1 ) 
);

declare
  t1id int;
begin
  
  insert into t1 values ( default )
  returning c1 into t1id;
  
  insert into t2 values ( t1id );
  
end;
/

select * from t1;

C1   
   1 

select * from t2;

T1_C1   
      1