[hibernate-dev] Mapping defaults for @OneToOne

Thu Dec 11 16:41:09 EST 2014

Are you asking me what "leveing rules" are?  ANSI SQL defines a number of
levels of conformance: entry, intermediate and full.  These leveling rules
describe additional rules/restrictions/limitations for certain conformance
level.

Taking a step back... a unique constraint says that the column(s) listed in
the constraint specification must contain unique values.  Well how do we
know values are unique?  We know because they are not equal to one
another.  And how does ANSI SQL define equality wrt NULL?  It says that
NULLs are never equal.  So how can they violate a unique constraint?

*If* we are to make any change here, I think what might be worthwhile is to
have the Dialect indicate what level of conformance it suports wrt unique
constraints.  If the Dialect reports FULL or INTERMEDIATE support, then we
would support building a UK on nullable columns; if it reports ENTRY, we
would not.
On Dec 11, 2014 2:54 PM, "Gail Badner" <gbadner at redhat.com> wrote:

> I check several dialects (can't remember exactly which ones)
>
> I see the following in
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt in 11.7 <unique
> constraint definition>:
>
> Leveling Rules
>
>          1) The following restrictions apply for Intermediate SQL:
>
>               None.
>
>          2) The following restrictions apply for Entry SQL in addition to
>             any Intermediate SQL restrictions:
>
>             a) If PRIMARY KEY or UNIQUE is specified, then the <column
> defi-
>               nition> for each column whose <column name> is in the <unique
>               column list> shall specify NOT NULL.
>
> To be honest, I don't know how these leveling rules are supposed to work.
> Do you?
>
> ----- Original Message -----
> > From: "Steve Ebersole" <steve at hibernate.org>
> > To: "Gail Badner" <gbadner at redhat.com>
> > Sent: Wednesday, December 10, 2014 5:07:37 PM
> > Subject: Re: [hibernate-dev] Mapping defaults for @OneToOne
> >
> > The point is the same.  ANSI SQL even states that unique keys should
> > disallow duplicate *non null* values but allow multiple rows with null.
> > Again, it just makes sense; null is *not* a value.
> >
> > Does some database not support that?  Yeah ok maybe
> > On Dec 10, 2014 2:46 PM, "Gail Badner" <gbadner at redhat.com> wrote:
> >
> > > The problem here is not that the FK has a null value. The problem is
> that
> > > the FK column has a unique constraint. It's the unique constraint that
> > > throws an exception when there is more than one null value for that
> column.
> > >
> > > I chatted with Emmanuel about this topic:
> > >
> > > (01:26:57 PM) emmanuel: gbadner: BTW if that's about your email on -dev
> > > and if for real DBs consider null as a unique value, then I think
> Hibernate
> > > doe sthr right thing by not enforcing
> > > (01:26:59 PM) emmanuel: it
> > >
> > > (01:37:13 PM) gbadner: emmanuel, what does that mean wrt to the spec?
> > > (01:37:37 PM) gbadner: is hibernate non-compliant, but does the right
> > > thing?
> > > (01:37:39 PM) emmanuel: the spec is not the gospel
> > > (01:38:10 PM) gbadner: there is an inconsistency between hbm.xml and
> > > annotations
> > > (01:39:02 PM) gbadner: the reason I care is that I have a fix for an
> issue
> > > that works for annotations, but not hbm.xml
> > > (01:39:34 PM) gbadner: the reason it works for annotations is because
> > > there is no unique constraint when the @OneToOne is optional
> > > (01:41:45 PM) gbadner: what *should* hibernate do when hbm.xml has:
> > > not-null="false" unique="true" ?
> > > (01:42:21 PM) emmanuel: hbm has always philosophically been close to
> the
> > > physicla model
> > > (01:42:22 PM) gbadner: currently, hibernate goes ahead and creates the
> > > unique constraint
> > > (01:42:34 PM) emmanuel: so applying the unique constraint would be
> what I
> > > expect
> > > (01:43:25 PM) gbadner: but should hibernate also override with
> > > not-null="true" when unique="true"?
> > > (01:44:03 PM) gbadner: ^^^ and give a warning?
> > > (01:45:30 PM) emmanuel: gbadner: I don't think I would expect that from
> > > hbm, no
> > > (01:45:49 PM) emmanuel: afterall it might be applicable for some
> database
> > > (01:45:50 PM) emmanuel: s
> > > (01:46:03 PM) emmanuel: (but even if it was not, hbm is explicit as I
> > > mentioned)
> > > (01:46:57 PM) gbadner: hibernate *knows* when a column is
> non-nullable; I
> > > don't think it knows when there is a unique constraint
> > > (01:47:26 PM) gbadner: well, I suppose the best Hibernate can do is
> just
> > > assume there is a unique constraint
> > >
> > > My fix for HHH-9091 (really it's a hack) is not acceptable because it
> will
> > > cause regressions for hbm.xml mappings.
> > >
> > > HHH-9091 is caused by HHH-6484. HHH-6484 made a change to execute
> orphan
> > > deletes before anything else, which causes a regression as illustrated
> in
> > >
> https://github.com/hibernate/hibernate-orm/commit/c068b6f70c84476db7a0cd26e80171ff69e51e90
> > > .
> > >
> > > At the very least, orphan-deletes must be executed in the proper
> order. I
> > > have to think more about whether are other dependencies that need to be
> > > taken into account.
> > >
> > > I've had to put HHH-9091 on hold while I fix HHH-8401, but will need to
> > > get back to it shortly.
> > >
> > > ----- Original Message -----
> > > > From: "Steve Ebersole" <steve at hibernate.org>
> > > > To: "Gail Badner" <gbadner at redhat.com>
> > > > Sent: Wednesday, December 10, 2014 8:45:13 AM
> > > > Subject: Re: [hibernate-dev] Mapping defaults for @OneToOne
> > > >
> > > > Actually, I would think databases would not treat null values in a FK
> > > > column as being a constraint violation.  Null is not a value.  Do you
> > > have
> > > > specific details about that concretely?  Databases I have worked
> with do
> > > > not behave that way.
> > > >
> > > > On Dec 2, 2014 11:34 PM, "Gail Badner" <gbadner at redhat.com> wrote:
> > > > >
> > > > > I'm a bit confused by the mapping defaults for @OneToOne defined
> in JPA
> > > > 2.1.
> > > > >
> > > > > Sections 2.10.1 and 2.10.3.1 (Bidirectional and Unidirectional
> OneToOne
> > > > Relationships) says, "The foreign key column has the same type as the
> > > > primary key of table B and there is a unique key constraint on it."
> > > > >
> > > > > Section 11.1.41 (OneToOne Annotation) says that, by default,
> > > > optional=true, which translates into nullable foreign key column(s).
> > > > >
> > > > > IIUC, the default mapping is not supported by many (all?)
> databases,
> > > > because a constraint violation exception will be thrown if a column
> has a
> > > > unique key constraint and there is more than one null value for that
> > > column.
> > > > >
> > > > > Currently, Hibernate does not create a unique key unless @OneToOne(
> > > > optional=false ) is defined. I believe Hibernate is doing the right
> thing
> > > > here, but it is not consistent with the spec.
> > > > >
> > > > > Is the spec wrong in this case, or am I missing something? Please
> let
> > > me
> > > > know...
> > > > >
> > > > > Thanks,
> > > > > Gail
> > > > > _______________________________________________
> > > > > hibernate-dev mailing list
> > > > > hibernate-dev at lists.jboss.org
> > > > > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> > > >
> > >
> >
>