FK Arc Trigger
In the preferences there is an option "Data Modeler > DDL > Create Triggers for FK arc constraints".
I think Oracle recomments to use constraints instead of triggers where it is possible.
Can anyone from the Development team tell me why here is just the option to create triggers and not also the option to create table level constraints for FK arc constraints?
If it is possible I'd like to have this option. My suggetion is to use the following check constraint construction for creation:
nvl2(FK_IDx,1,0)+nvl2(FK_IDy,1,0)+...+nvl2(FK_IDz,1,0)=1
or with not only single FK columns:
nvl2(FK_IDx1,1,0)+nvl2(FK_IDx2,1,0)+nvl2(FK_IDy,2,0)+...+nvl2(FK_IDz,2,0)=2.
More abstract:
let n be the number of foreign keys in the arc
let IDik be the k-th column in the i-th foreign key
let IDiMax be the number of columns in the i-th foreign key
let ArcMax be the maximum of columns used in a foreign key in the arc
then the constraint should be
nvl2(ID11,1,0)+nvl2(ID12,1,0)+...+nvl2(ID1Max,ArcMax-1Max+1,0)+
nvl2(ID21,1,0)+nvl2(ID22,1,0)+...+nvl2(ID2Max,ArcMax-2Max+1,0)+
...+
nvl2(IDn1,1,0)+nvl2(IDn2,1,0)+...+nvl2(IDnMax,ArcMax-nMax+1,0)
=ArcMax
Please tell me if that would be possible (and maybe available in next Version) or why it is better to user triggers instead.
Thanks
/c