wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION

[tzaenker]

Hi,
in Activiti 6 I have found an Oracle Foreign Key from ACT_RU_TASK column PROC_INST_ID_ to ACT_RU_EXECUTION.PROC_INST_ID_

This is a little bit incorrect because the PROC_INST_ID_ of the Excecution side can appear multiple times. This blows my hibernate up when I use my own queries.

Shouldn't the Foreign key point to a unique column like ACT_HI_PROCINST.ID_ ?

[jbarrez]

The Oracle create script has this:

create index ACT_IDX_TASK_EXEC on ACT_RU_TASK(EXECUTION_ID_);
alter table ACT_RU_TASK
    add constraint ACT_FK_TASK_EXE
    foreign key (EXECUTION_ID_)
    references ACT_RU_EXECUTION (ID_);
   
create index ACT_IDX_TASK_PROCINST on ACT_RU_TASK(PROC_INST_ID_);
alter table ACT_RU_TASK
    add constraint ACT_FK_TASK_PROCINST
    foreign key (PROC_INST_ID_)
    references ACT_RU_EXECUTION (ID_);

That looks allright to me. The task has an FK to the corresponding execution and to the corresponding process instance. I don't see it pointing to ACT_RU_EXECUTION.PROC_INST_ID_ though, but to ID_.

Any idea how you'd got to your schema as it is?

[tzaenker]

This is the point I mean.
a Foreign key defines a many to one relationship.
Means when I look up beginning from the ACT_RU_TASK and I look for the Execution by Excecution_ID_ I get 1 element back. Which is good!
When I look for Process instance I retrieve all Executions and the process instance itself. Which is wrong and confusing. Why PROC_INST_ID_ is not pointing to ACT_RU_EXECUTION.ID_ referencing the one Process instance instead of all child executions of this process?

To give you one example:

ID_  REV_ PROC_INST_ID_ BUSINESS_KEY_ PARENT_ID_ PROC_DEF_ID_
2505  1 2505   iteratorExample:1:2504
2509  1 2505  2505 iteratorExample:1:2504
2512  1 2505  2509 iteratorExample:1:2504
2533  1 2505  2512 iteratorExample:1:2504

When I am looking from a task for the proc_inst_id_ (following the Foreign Key) I retrieve four "Process instances" instead of only one. In this example above I would expect to retrieve only the ID_ 2505 entry instead of the set of 2505 - 2533.

The definition in the database is wrong at this point.

[tzaenker]

I read again your post from before. And sorry your SQL is correct. I don't know why is it wrong in my Database. I will check my create scripts.