You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Charles Moulliard <cm...@gmail.com> on 2009/12/07 17:05:13 UTC
Violation of unique constraint $$: duplicate value(s) for column
Hi,
I use openjpa version 1.3-snasphot and the following entity class which is
used in my application generates the following error :
@Entity
@Table(
name = "CAMEL_ACTIVITYDEFINITION",
uniqueConstraints = @UniqueConstraint(columnNames = {"name"})
)
public class ActivityDefinition extends EntitySupport {
private String name;
private ProcessDefinition processDefinition;
// This crap is required to work around a bug in hibernate
@Override
@Id
@GeneratedValue
public Long getId() {
return super.getId();
}
@Override
public String toString() {
return "Activity[" + getId() + " name: " + getName() + "]";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST,
CascadeType.MERGE })
public ProcessDefinition getProcessDefinition() {
return processDefinition;
}
public void setProcessDefinition(ProcessDefinition processDefinition) {
this.processDefinition = processDefinition;
}
}
ERROR :
Caused by: <openjpa-1.3.0-SNAPSHOT-r422266:881892 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: Violation of unique
constraint $$: duplicate value(s) for column(s) $$: U_CML_NTN_NAME in
statement [INSERT INTO CAMEL_PROCESSDEFINITION (id, name) VALUES (?, ?)]
{prepstmnt 30276889 INSERT INTO CAMEL_PROCESSDEFINITION (id, name) VALUES
(?, ?) [params=(long) 7, (String) ]} [code=-104, state=23000]
FailedObject: org.apache.camel.bam.model.ProcessDefinition@17fc44f
at
org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4351)
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4316)
at
org.apache.openjpa.jdbc.sql.HSQLDictionary.newStoreException(HSQLDictionary.java:292)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:127)
at
org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:80)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:93)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:81)
at
org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:549)
at
org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
at
org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
at
org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
at
org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:719)
at
org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
... 29 more
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Violation of
unique constraint $$: duplicate value(s) for column(s) $$: U_CML_NTN_NAME in
statement [INSERT INTO CAMEL_PROCESSDEFINITION (id, name) VALUES (?, ?)]
{prepstmnt 30276889 INSERT INTO CAMEL_PROCESSDEFINITION (id, name) VALUES
(?, ?) [params=(long) 7, (String) ]} [code=-104, state=23000]
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:983)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1601)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:220)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:112)
... 39 more
Can someone tells me how to avoid that ?
Regards,
Charles Moulliard
Senior Enterprise Architect
Apache Camel Committer
*****************************
blog : http://cmoulliard.blogspot.com
twitter : http://twitter.com/cmoulliard
Linkedlin : http://www.linkedin.com/in/charlesmoulliard
Apache Camel Group :
http://www.linkedin.com/groups?home=&gid=2447439&trk=anet_ug_hm
Re: Violation of unique constraint $$: duplicate value(s) for
column
Posted by cmoulliard <cm...@xpectis.com>.
I have set the field name with a default value in the class ProcessDefinition
but the error is still there. If I remove the annotation on the method
getName, it works !!!
//@Column(unique = true)
public String getName() {
return name;
}
What is the solution for HSQL DB ?
Remark : Even if the reason of the message displayed in the error is that
the field null, the message displayed is not very helpfull for the user
(Violation of unique constraint $$: duplicate value(s) for column) --> must
Violation of unique constraint $$: cannot insert null value(s) for column
!!!
Regards,
Charles
Daryl Stultz wrote:
>
> On Mon, Dec 7, 2009 at 11:37 AM, KARR, DAVID (ATTCINW)
> <dk...@att.com>wrote:
>
>> It doesn't make sense to have a unique constraint on a
>> NULLABLE column.
>>
>
> I disagree. A property can be optional and be required to be unique when
> it
> is specified. In my application I have such a case. I omitted the Unique
> annotation from my class but left the constraint in the database (OpenJPA
> refused to insert null into a foreign key column). Using PostgreSQL, nulls
> are ignored regarding uniqueness.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> mailto:daryl@6degrees.com
>
>
--
View this message in context: http://n2.nabble.com/Violation-of-unique-constraint-duplicate-value-s-for-column-tp4126767p4131845.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Violation of unique constraint $$: duplicate value(s) for column
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Dec 8, 2009 at 4:00 AM, Thomas Polliard <th...@polliard.com> wrote:
> Perhaps I dont understand your goals but.....
>
>
> If you intend for the field to ALWAYS have a value for the row to be valid
> then NOT NULL is required. If you intend for the rows to be unique but dont
> require the field for the row to be valid, it might make more sense to use a
> separate table. Having a column on a table that is UNIQUE but NULLABLE is a
> bad design and can lead to confusion.
>
>
I have a users table with a timecardid column. A user may or may not have a
timecardid (it's just a text value, not a foreign key). If they have it, it
must be unique (nulls excluded). DDL looks like this:
CREATE TABLE users (
userid integer NOT NULL,
...
timecardid character varying(50),
...
);
CREATE UNIQUE INDEX users_timecardid_unq ON users USING btree (timecardid);
It accepts nulls and rejects (non-null) duplicates:
db=# select count(*) from users where timecardid is null;
count
-------
62
(1 row)
There are often a variety of ways of physically modelling the logical
design. Adding a one-to-one table adds an unnecessary complication. What's
confusing about "it's optional but needs to be unique if provided"?
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Violation of unique constraint $$: duplicate value(s) for column
Posted by Thomas Polliard <th...@polliard.com>.
Perhaps I dont understand your goals but.....
Postgres:
create table test (
ID SERIAL PRIMARY KEY,
name varchar(255) NULL UNIQUE
);
insert into test(name) values(NULL);
insert into test(name) values(NULL); // WILL RESULT in contraint
failure and should
insert into test(name) values('A');
If you intend for the field to ALWAYS have a value for the row to be
valid then NOT NULL is required. If you intend for the rows to be
unique but dont require the field for the row to be valid, it might
make more sense to use a separate table. Having a column on a table
that is UNIQUE but NULLABLE is a bad design and can lead to confusion.
Thomas
On Dec 8, 2009, at 3:46 AM, cmoulliard wrote:
>
> I have set the field name with a default value in the class
> ProcessDefinition
> but the error is still there. If I remove the annotation on the method
> getName, it works !!!
>
> //@Column(unique = true)
> public String getName() {
> return name;
> }
>
> What is the solution for HSQL DB ?
>
> Remark : Even if the reason of the message displayed in the error is
> that
> the field null, the message displayed is not very helpfull for the
> user
> (Violation of unique constraint $$: duplicate value(s) for column) --
> > must
> Violation of unique constraint $$: cannot insert null value(s) for
> column
> !!!
>
> Regards,
>
> Charles
>
>
> Daryl Stultz wrote:
>>
>> On Mon, Dec 7, 2009 at 11:37 AM, KARR, DAVID (ATTCINW)
>> <dk...@att.com>wrote:
>>
>>> It doesn't make sense to have a unique constraint on a
>>> NULLABLE column.
>>>
>>
>> I disagree. A property can be optional and be required to be unique
>> when
>> it
>> is specified. In my application I have such a case. I omitted the
>> Unique
>> annotation from my class but left the constraint in the database
>> (OpenJPA
>> refused to insert null into a foreign key column). Using
>> PostgreSQL, nulls
>> are ignored regarding uniqueness.
>>
>> --
>> Daryl Stultz
>> _____________________________________
>> 6 Degrees Software and Consulting, Inc.
>> http://www.6degrees.com
>> mailto:daryl@6degrees.com
>>
>>
>
> --
> View this message in context: http://n2.nabble.com/Violation-of-unique-constraint-duplicate-value-s-for-column-tp4126767p4131234.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Violation of unique constraint $$: duplicate value(s) for
column
Posted by cmoulliard <cm...@xpectis.com>.
I have set the field name with a default value in the class ProcessDefinition
but the error is still there. If I remove the annotation on the method
getName, it works !!!
//@Column(unique = true)
public String getName() {
return name;
}
What is the solution for HSQL DB ?
Remark : Even if the reason of the message displayed in the error is that
the field null, the message displayed is not very helpfull for the user
(Violation of unique constraint $$: duplicate value(s) for column) --> must
Violation of unique constraint $$: cannot insert null value(s) for column
!!!
Regards,
Charles
Daryl Stultz wrote:
>
> On Mon, Dec 7, 2009 at 11:37 AM, KARR, DAVID (ATTCINW)
> <dk...@att.com>wrote:
>
>> It doesn't make sense to have a unique constraint on a
>> NULLABLE column.
>>
>
> I disagree. A property can be optional and be required to be unique when
> it
> is specified. In my application I have such a case. I omitted the Unique
> annotation from my class but left the constraint in the database (OpenJPA
> refused to insert null into a foreign key column). Using PostgreSQL, nulls
> are ignored regarding uniqueness.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> mailto:daryl@6degrees.com
>
>
--
View this message in context: http://n2.nabble.com/Violation-of-unique-constraint-duplicate-value-s-for-column-tp4126767p4131234.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Violation of unique constraint $$: duplicate value(s) for column
Posted by Daryl Stultz <da...@6degrees.com>.
On Mon, Dec 7, 2009 at 11:37 AM, KARR, DAVID (ATTCINW) <dk...@att.com>wrote:
> It doesn't make sense to have a unique constraint on a
> NULLABLE column.
>
I disagree. A property can be optional and be required to be unique when it
is specified. In my application I have such a case. I omitted the Unique
annotation from my class but left the constraint in the database (OpenJPA
refused to insert null into a foreign key column). Using PostgreSQL, nulls
are ignored regarding uniqueness.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
RE: Violation of unique constraint $$: duplicate value(s) for column
Posted by "KARR, DAVID (ATTCINW)" <dk...@att.com>.
> -----Original Message-----
> From: Charles Moulliard [mailto:cmoulliard@gmail.com]
> Sent: Monday, December 07, 2009 8:05 AM
> To: users@openjpa.apache.org
> Subject: Violation of unique constraint $$: duplicate value(s) for
> column
>
> Hi,
>
> I use openjpa version 1.3-snasphot and the following entity class
which
> is
> used in my application generates the following error :
>
> [deleted]
> ERROR :
>
> Caused by: <openjpa-1.3.0-SNAPSHOT-r422266:881892 nonfatal general
> error>
> org.apache.openjpa.persistence.PersistenceException: Violation of
> unique
> constraint $$: duplicate value(s) for column(s) $$: U_CML_NTN_NAME in
> statement [INSERT INTO CAMEL_PROCESSDEFINITION (id, name) VALUES (?,
> ?)]
> {prepstmnt 30276889 INSERT INTO CAMEL_PROCESSDEFINITION (id, name)
> VALUES
> (?, ?) [params=(long) 7, (String) ]} [code=-104, state=23000]
It sure looks to me like you're trying to insert a row with a NULL
"name" value. It doesn't make sense to have a unique constraint on a
NULLABLE column.