topic Re: wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION in Alfresco Archive

wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION

tzaenker — Fri, 17 Jun 2016 14:07:13 GMT

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 For

Re: wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION

jbarrez — Mon, 27 Jun 2016 10:11:38 GMT

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?

Re: wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION

tzaenker — Mon, 27 Jun 2016 12:58:53 GMT

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.

Re: wrong FK ACT_RU_TASK.PROC_INST_ID to ACT_RU_EXECUTION

tzaenker — Mon, 27 Jun 2016 13:16:15 GMT

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.