NOT NULL constraint on index columns?

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

I'd like to add a NOT NULL constraint to my index columns for sorted collections. Since Hibernate throws an exception when it tries to fetch a collection with a null index column, it seems like this would catch the problem at the point of writing instead of waiting until reading (ie. if I forget to set both ends of a relationship).

First question: Is this a good idea or will it cause problems?

Second question: I'm using the Hibernate schema updating feature - is there something I can set in the mapping file to avoid having to manually tweak the SQL generation?

Hibernate version: 3.05

nathanmoon · **Posted:** Mon Aug 08, 2005 2:10 pm

jbarnum wrote:

Since Hibernate throws an exception when it tries to fetch a collection with a null index column

Do you really mean 'index column'?. You seem to be talking about associations (foreign key columns). And what exception are you seeing?

jbarnum wrote:

First question: Is this a good idea or will it cause problems?

My opinion is that it's always best to enforce constraints and nullability in both the database and the code.

jbarnum wrote:

Second question: I'm using the Hibernate schema updating feature - is there something I can set in the mapping file to avoid having to manually tweak the SQL generation?

Have you set the not-null="true" attribute on the properties in your mapping file? SchemaExport will use these attributes when creating the tables.

Post your mapping files if you are still having troubles.

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

Hi Nathan, thanks for the reply.

I'm not talking about foreign key constraints, I'm talking about index columns - when you have an indexed collection (like a List), an extra column must exist in the database to store the sort order for that collection. Hibernate throws an exception if the index column is null for a sorted collection when it tries to fetch from the database.

As far as I can tell, there is no way to specify not-null="true" for an index column, I'm hoping there is some trick to doing it that I don't know about. Here's an example of a mapping file with an indexed collection (scripts):

Code:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.portalvideo.model" default-cascade="lock" default-access="field">
   <class lazy="false" name="Project">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="name" type="java.lang.String"/>
      <property name="description" type="java.lang.String"/>
      <many-to-one name="person" column="personId"/>
      <list name="scripts" lazy="true" inverse="false">
         <key column="projectId"/>
         <index column="sortOrderForproject"/>
         <one-to-many class="Script"/>
      </list>
      <many-to-one name="level2Client" column="level2ClientId"/>
   </class>
</hibernate-mapping>

nathanmoon · **Posted:** Mon Aug 08, 2005 4:49 pm

My bad, I totally mis-read your post!

I haven't used indexed collections before, but it seems like Hibernate (SchemaExport) would make that column not-null by default.

gavin · **Posted:** Mon Aug 08, 2005 7:44 pm

Just make sure you use <key not-null="true"/>

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

gavin wrote:

Just make sure you use <key not-null="true"/>

This is not for a key, it's for a column index. When I try to add not-null="true", like this:

Code:

<list name="scripts" lazy="true" access="field">
    <key column="projectId"/>
    <index column="sortOrderForproject" not-null="true"/>
    <one-to-many class="Script"/>
</list>

I get an XML parsing error.

pksiv · **Posted:** Mon Aug 08, 2005 8:09 pm

jbarnum wrote:

gavin wrote:

Just make sure you use <key not-null="true"/>

This is not for a key, it's for a column index. When I try to add not-null="true", like this:

Code:

<list name="scripts" lazy="true" access="field">
    <key column="projectId"/>
    <index column="sortOrderForproject" not-null="true"/>
    <one-to-many class="Script"/>
</list>

I get an XML parsing error.

There is a mapping file for the Script class, or whatever your one-to-many class is, that also contains the index column right? You should be able to map that as not-null="true"

gavin · **Posted:** Mon Aug 08, 2005 8:56 pm

The <key> and <index> columns can not be independantly non-null. The setting for <key> determines both.

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

gavin wrote:

The <key> and <index> columns can not be independantly non-null. The setting for <key> determines both.

Thanks Gavin, this worked - now when I forget to save an inverse relationship, I get an immedate exception when I save like this:

org.hibernate.PropertyValueException: not-null property references a null or transient value: com.prosc.test.Course._coursesBackref

Pretty cool!

One weird thing was this - since I am doing this as a bidirectional one-to-many with a List and inverse="false" at the many end, I needed to set update="false" and insert="false" in the mapping file. Without that, Hibernate complained about a duplicate column in the mapping file. With that done though, it's working fine.

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

pksiv wrote:

There is a mapping file for the Script class, or whatever your one-to-many class is, that also contains the index column right? You should be able to map that as not-null="true"

No, there is not a mapping for that property in the Script class. The only appearance of that field anywhere in any mapping document is in the <index> element of the Project mapping file. It's treated by Hibernate similar to a key field - you don't map it as a property, it is just automatically updated behind the scenes (although you have to violate all the rules about inverse relationships to make it work).

gavin · **Posted:** Tue Aug 09, 2005 2:00 am

jbarnum wrote:

One weird thing was this - since I am doing this as a bidirectional one-to-many with a List and inverse="false" at the many end, I needed to set update="false" and insert="false" in the mapping file. Without that, Hibernate complained about a duplicate column in the mapping file. With that done though, it's working fine.

This "wierd thing" is properly mentioned in the HB 3.1 docs.

dlockhart · **Joined:** Tue Jul 12, 2005 4:57 pm **Posts:** 1

Can you post your final working xml? I'm struggling with the same problem and still can't get my code to work.

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

dlockhart wrote:

Can you post your final working xml? I'm struggling with the same problem and still can't get my code to work.

Here's the relationship between Projects and Scripts. Projects have many Scripts, scripts are required to have one project. I've gotten rid of all of the properties and other relationships to reduce clutter. I should also point out that I don't do any cascades in my Hibernate mapping files, those are configured at runtime in my Java code that sets up the SessionFactory, so I would probably recommend configuring cascading between a parent-child relationship like this one.

Code:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.portalvideo.model" default-cascade="lock" default-access="field">
   <class lazy="false" name="Project">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <list name="scripts" lazy="true" inverse="false">
         <key column="projectId" not-null="true"/>
         <index column="sortOrderForproject"/>
         <one-to-many class="Script"/>
      </list>
   </class>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.portalvideo.model" default-cascade="lock" default-access="field">
   <class lazy="false" name="Script">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <many-to-one name="project" column="projectId" not-null="true" update="false" insert="false"/>
   </class>
</hibernate-mapping>

jbarnum · **Joined:** Wed May 11, 2005 11:57 pm **Posts:** 80

gavin wrote:

The <key> and <index> columns can not be independantly non-null. The setting for <key> determines both.

While it seems that this worked at first, we still had a few null values slipping through - not sure how. When I check the actual database schema, I noticed that the index column is set to allow nulls. Shouldn't the UpdateSchema class be setting these to not allow nulls?

Code:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.portalvideo.model" default-cascade="lock" default-access="field">
   <class lazy="false" name="Bin">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="name" type="java.lang.String"/>
      <property name="creationId" type="java.lang.Long"/>
      <list name="scriptNuggets" lazy="true" inverse="false">
         <key column="binId" not-null="true"/>
         <index column="sortOrderForbin"/>
         <one-to-many class="ScriptNugget"/>
      </list>
      <many-to-one name="scriptVersion" column="scriptVersionId" not-null="true" update="false" insert="false"/>
   </class>
</hibernate-mapping>

gavin · **Posted:** Thu Aug 18, 2005 5:20 pm

No SchemaUpdate cannot create non-null columns. (It's obvious when you think about it.)

Use SchemaExport.