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(a)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(a)hibernate.org>
> To: "Gail Badner" <gbadner(a)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(a)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/c068b6f70c84476db7a0cd2...
> > .
> >
> > 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(a)hibernate.org>
> > > To: "Gail Badner" <gbadner(a)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(a)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(a)lists.jboss.org
> > > >
https://lists.jboss.org/mailman/listinfo/hibernate-dev
> > >
> >
>