CFML ORM — NOT NULL Constraints, Cascade, and Inverse

I’m deciding to make mini-blogs about the things I find out using CFML ORM. Hopefully these entries will save you time and sweat later.

I think it’s generally a good practice to leave properties as NOT NULL by default. I tend to see fewer bugs like orphan records that way. But I’ve ran in to an issue over and over again when dealing with relationships and the cascade attribute.

Maybe you have this same use case — a one-to-many relationship to which you want to cascade changes. Maybe an User and Comment? (A User component is usually my culprit here.)

All seems pretty straight forward, right? But when I try to delete a user, I get an error like the following:

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'FK_user', table 'my_app.dbo.comments'; column does not allow nulls. UPDATE fails.

This has confused me like crazy. I don’t want NULL in the column. I want to delete it. Usually my fix has been to remove notnull="true" from the comments side of the relationship. (And I’m not the only one.) This works, but has all the drawbacks of having a nullable field when we don’t want to.

So what’s the fix?

inverse="true"

Yup. That’s it. I found the answer posted in the comment thread of an older article by Nicholas Tunney.

Make sure to add it to entity that owns the foreign key (User in our case).

There’s a good write-up here on inverse in Hibernate that helped clear some things up for me. The gist of it is that setting inverse="true" takes the SQL down from two inserts and an update to just two inserts.

Anyway, I’ve been bit enough by this behavior to document it here. Hope this helps someone else.

CFML ORM — NOT NULL Constraints, Cascade, and Inverse