How do I map entities in GORM when pk of one references pk of another

Question

What is the best way to map two entities in GORM when the primary key of one table is also a foreign key from another table. For ex:

Here is one table:

CREATE TABLE `table_a` (
`a_id` int(11) NOT NULL AUTO_INCREMENT, 
PRIMARY KEY (`a_id`)
)

And the pk of this table 'a_id' is referenced by the following table:

CREATE TABLE `table_b` (
`b_id` int(11) NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY (`b_id`), 
 KEY `b_id_fk` (`b_id`), 
 CONSTRAINT `b_id_fk` FOREIGN KEY (`b_id`) REFERENCES `table_a' (`a_id`)
 )

How do I map the above two entities in Grails/GORM? The following code will obviously not work since GORM will throw exception saying 'repeated column in mapping for entity'.

class TableB {

 TableA tableA
 static belongsTo = [TableA]

 static mapping = {
    id column:"b_id"
    version false
    tableA column:"b_id"
 }

 static constraints = {
    tableA unique: true
 }
}

Note: I'm using grails 1.3.7 but can switch to a newer version if this problem is an issue that has recently been fixed. Please let me know.

can you change the schema or do you have to have the primary key also be the fk? — hvgotcodes
I know the schema is not as per best practices. But it's a legacy database which I cannot change. — sul

Gustavo Giráldez Gustavo Giráldez · Accepted Answer · 2011-06-15T02:16:47

I don't know if it applies to your case, but one thing you can do is map TableB as a subclass of TableA using table-per-subclass inheritance strategy. Check Inheritance Strategies in the Grails guide.

So, you'd have:

class TableA {
    static mapping = {
        id column: 'a_id'
        table 'table_a'
        version false
        tablePerHierarchy false
    }
}

class TableB extends TableA {
    static mapping = {
        id column: 'b_id'
        table 'table_b'
        version false
    }
}

The problem with this approach is that you can't create a TableB object after you have created the TableA object. The way Hibernate works, when you create a new TableA instance, a record in table_a is created. When you create a new TableB instance, both a record in table_a and a record in table_b are created, with the same id.

OTOH, I can't think of a valid reason for that schema other than the mapping of a class hierarchy like this.

How do I map entities in GORM when pk of one references pk of another

1 Answers