You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Daryl Stultz <da...@6degrees.com> on 2009/06/26 14:15:11 UTC
Nullable unique constraints
Hello,
I am finding that OpenJPA does not handle my unique constraints the way I
expect. When I have a nullable column, say a foreign key that is an integer,
and it must be unique (if not null), OpenJPA inserts a 0 instead of null,
which violates the FK constraint, of course. Currently I am using the unique
constraint on the database but omitting it from the entity metadata (and
wondering what benefit I might be sacrificing - the manual speaks only of
table creation DDL and ordering SQL statements). Is there a way to define a
Unique Constraint that allows null?
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Daryl Stultz <da...@6degrees.com>.
On Fri, Nov 13, 2009 at 10:02 AM, Martin Dirichs <nabble@dirichs.fastmail.fm
> wrote:
>
> This seems to be an OpenJPA issue.
...
> https://issues.apache.org/jira/browse/OPENJPA-1387
>
>
Thanks Martin!
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Martin Dirichs <na...@dirichs.fastmail.fm>.
Daryl Stultz wrote:
>
> When I define my field as "unique = true, nullable = true" and then run
> the
> mapping tool to build the DDL it generates the column as "not null". It
> does
> seem to be that null is incompatible with unique. Is this an OpenJPA thing
> or a JPA spec?
>
This seems to be an OpenJPA issue. I also had the same question as you and
so far got no explanation for this behaviour of OpenJPA. The JPA spec
doesn't mention this coupling of unique constraints and non-nullability. I
have filed a corresponding issue here:
https://issues.apache.org/jira/browse/OPENJPA-1387
(This reply is rather late; I came across your request via an explicit
search)
--
View this message in context: http://n2.nabble.com/Nullable-unique-constraints-tp3161182p3999677.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Nullable unique constraints
Posted by Daryl Stultz <da...@6degrees.com>.
On Tue, Jun 30, 2009 at 9:29 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
> Does anybody have an example they can share of a unique nullable field? It
> doesn't have to be a foreign key, I'm having trouble with a String field as
> well.
>
>
>
When I define my field as "unique = true, nullable = true" and then run the
mapping tool to build the DDL it generates the column as "not null". It does
seem to be that null is incompatible with unique. Is this an OpenJPA thing
or a JPA spec?
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Daryl Stultz <da...@6degrees.com>.
On Mon, Jun 29, 2009 at 8:00 PM, Adam Hardy <ad...@cyberspaceroad.com>wrote:
>
> Also try insertable=false updatable=false
>
When I add this, I no longer get the insert of 0 for null. I also get null
for everything. As the property name suggests, it doesn't get updated or
inserted. According to the docs:
> -
>
> boolean insertable: By setting this property to false, you can omit the
> column from SQL INSERT statements. Defaults to true.
> -
>
> boolean updatable: By setting this property to false, you can omit the
> column from SQL UPDATE statements. Defaults to true.
>
> This doesn't make any sense. Who is "you"? As a user of JPA, I don't create
SQL statements myself. What does "you can omit" mean? If I feel like it? It
doesn't seem like this is related to my problem.
>
> and set the openjpa property
> openjpa.jdbc.SchemaFactory=native(ForeignKeys=true)
This appears to be a good development practice to ensure my database schema
is correct. It doesn't actually change the behavior, right? It didn't cause
any exceptions, I don't think.
Does anybody have an example they can share of a unique nullable field? It
doesn't have to be a foreign key, I'm having trouble with a String field as
well.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Adam Hardy <ad...@cyberspaceroad.com>.
Daryl Stultz on 29/06/09 16:26, wrote:
> On Mon, Jun 29, 2009 at 11:21 AM, Daryl Stultz <da...@6degrees.com> wrote:
>
>> I don't see "unique" as an option to @Column and "nullable" defaults to
>> true. Maybe you can give me an example.
>>
>
> Sorry, my mistake, I see it on @JoinColumn. I now have this:
>
> @UniqueConstraint(columnNames = "entityid")
> ...
> @JoinColumn(name = "entityid", nullable = true, unique = true)
>
> But it is still trying to insert 0.
Also try insertable=false updatable=false
and set the openjpa property openjpa.jdbc.SchemaFactory=native(ForeignKeys=true)
Re: Nullable unique constraints
Posted by Daryl Stultz <da...@6degrees.com>.
On Mon, Jun 29, 2009 at 11:21 AM, Daryl Stultz <da...@6degrees.com> wrote:
> I don't see "unique" as an option to @Column and "nullable" defaults to
> true. Maybe you can give me an example.
>
Sorry, my mistake, I see it on @JoinColumn. I now have this:
@UniqueConstraint(columnNames = "entityid")
...
@JoinColumn(name = "entityid", nullable = true, unique = true)
But it is still trying to insert 0.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Daryl Stultz <da...@6degrees.com>.
On Fri, Jun 26, 2009 at 5:56 PM, Adam Hardy <ad...@cyberspaceroad.com>wrote:
> Daryl Stultz on 26/06/09 13:15, wrote:
>
> Presumably you have tried setting the column definition in your metadata to
> nullable=true and unique=true ?
Can you be more specific? Are you referring to @Column or columnDefinition
within @Column? I don't think columnDefinition would be much help since it
only provides help in generating the DDL. I don't see "unique" as an option
to @Column and "nullable" defaults to true. Maybe you can give me an
example.
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: Nullable unique constraints
Posted by Adam Hardy <ad...@cyberspaceroad.com>.
Daryl Stultz on 26/06/09 13:15, wrote:
> I am finding that OpenJPA does not handle my unique constraints the way I
> expect. When I have a nullable column, say a foreign key that is an integer,
> and it must be unique (if not null), OpenJPA inserts a 0 instead of null,
> which violates the FK constraint, of course. Currently I am using the unique
> constraint on the database but omitting it from the entity metadata (and
> wondering what benefit I might be sacrificing - the manual speaks only of
> table creation DDL and ordering SQL statements). Is there a way to define a
> Unique Constraint that allows null?
Presumably you have tried setting the column definition in your metadata to
nullable=true and unique=true ?