• Post Reply Bookmark Topic Watch Topic
  • New Topic

Problems changing foreign key references in Grails?

chris webster
Posts: 2407
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Has anybody else out there had problems changing foreign key references in Grails?

I am fairly new to Java/Grails, and I'm trying to build an Oracle-based system that will have a number of reference data tables, plus a few application data tables. The ref data table records typically have a CODE and DESCRIPTION, and the application data records use these codes. If possible, I want the child records (the app data) to contain the actual code for a reference record, rather than just a surrogate numeric ID, because the data will make sense more easily to users who access it via SQL etc.

By analogy, if I had a Department (String deptCode, String deptName), and a Department can have many Employees, then the Employee's FK back to the Department should be the deptCode, not an arbitrary ID. And an Employee should be able to change Departments i.e. I need to be able to update the FK association.

Of course, by default, Grails/Hibernate adds a numeric surrogate ID to each class/table, which is populated from a DB sequence in Oracle, and uses that as the primary key.

If I go ahead and use the default ID keys, I can switch an Employee from one Department to another with no problems, but I would have to join back to the Department table if I wanted to find the Employee's deptCode via SQL. Not hard, but slightly more work for ad hoc users.

Alternatively, if I tell Grails to use the deptCode as the PK for Department, then it uses the deptCode for the Employee's FK association back to Department, just as I want, so my ad hoc SQL users don't have to bother with the join.

But when I try to change the Employee's Department reference (via a <g:select> box in the "edit" view), I get a Hibernate exception, telling me I have tried to change the deptCode for the parent Department (instead of simply changing the Employee's reference to point to a different Department).

By poking around in the controller, I have established that the correct value is being passed back from the view, but that it blows up inside the black box of the domain class's "save" method. This is weird, and as I say, the problem does not occur if I use the default generated ID keys.

I've searched around online and found a few examples of people having the same problem, right back to very early versions of Grails. But so far I can't find a reliable solution.

Does anybody out there know anything about this?

Or will I just have to go back to using surrogate IDs instead?

Thanks for any help!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!