[ http://jira.jboss.com/jira/browse/JBPM-913?page=comments#action_12358106 ] David Lloyd commented on JBPM-913: ---------------------------------- The main issue here is that most databases (including mysql and oracle) do not index null values (if you think about it, you'll see why). The appropriate workaround for your case is probably to replace the unique constraint with a standard index on (KEY_, PROCESSDEFINITION_). The permanent fix is under discussion. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://jira.jboss.com/jira/browse/JBPM-913?page=comments#action_12360249 ] David Lloyd commented on JBPM-913: ---------------------------------- er, change setConstraint to setUniqueKey in that example. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://jira.jboss.com/jira/browse/JBPM-913?page=comments#action_12362231 ] Tom Baeyens commented on JBPM-913: ---------------------------------- i've checked postgresql, hsqldb and oracle. they all claim to allow null values in columns with a unique constraint. so you'll have to come up with more arguments for your case. feel free to reopen this case when you supply more information. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://jira.jboss.com/jira/browse/JBPM-913?page=comments#action_12362280 ] Olga Ivanova commented on JBPM-913: ----------------------------------- Please correct me if I'm wrong but I believe null values are allowed with unique constraint when all columns in a constraint have null value. In our case when key consists of process definition id and 'key' field, process definition id is never null, so it won't be possible to insert two process instances that belong to same process definition, where 'key' field is null. I've tried to test this on Oracle with this data: create table ri_unique ( a number, b number, unique(a, b) ); then try to insert some data insert into ri_unique values (1, 1) // ok insert into ri_unique values (null, 1) // ok insert into ri_unique values (null, 1) // failed, unique constraint violated insert into ri_unique values (null, null) // ok insert into ri_unique values (null, null) // ok Having one additional unique key, as David proposed will solve the problem I think. I believe issue should be reopened. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira

[ http://jira.jboss.com/jira/browse/JBPM-913?page=all ] David Lloyd reopened JBPM-913: ------------------------------ Yes, I know these databases allow null values in a constraint column. But the semantics work like this: If KEY_ is null and PROCESSDEFINITION_ is null, no entry is made into the unique index. THIS is the behavior that matches what you are talking about. If KEY_ is null and PROCESSDEFINITION_ is NOT null, an entry is made into the index with just the process definition ID. Since KEY_ is null, that part of the index is "blank". Therefore an insert of another process instance with a process definition but no key will cause a constraint violation. That's why I suggested that if this constraint is to exist in any form, then there should be a single column that is null unless both the key and process definition is specified, in which case the string value of the column would be something like "123:ProcessKey" where 123 is the process definition ID. Otherwise this constraint should be removed. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira