Yes, that makes good sense. I see the info is available via DatabaseMetaData.supportsANSI92EntryLevelSQL(), supportsANSI92IntermediateSQL(), and supportsANSI92FullSQL(). Hmm, what if DatabaseMetaData is not available. Do we assume ENTRY? ----- Original Message ----- > From: "Steve Ebersole" <steve(a)hibernate.org&gt; > To: "Gail Badner" <gbadner(a)redhat.com&gt;, "hibernate-dev" < hibernate-dev(a)lists.jboss.org&gt; > Sent: Thursday, December 11, 2014 1:41:09 PM > Subject: Re: [hibernate-dev] Mapping defaults for @OneToOne > > 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&gt; 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&gt; > > > To: "Gail Badner" <gbadner(a)redhat.com&gt; > > > 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&gt; 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&gt; > > > > > To: "Gail Badner" <gbadner(a)redhat.com&gt; > > > > > 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&gt; 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 > > > > > > > > > > > > > > >