How to Set up a Foreign Key Constraint in MySQL

The following is a simple example that illustrates Foreign Key constraints:

Creating a table for manager:
CREATE TABLE manager (id integer primary key auto_increment, name text) ENGINE=InnoDB;

Creating a table for club and creating a foreign key constraint for manager_id with key 'manager_id_reference':
CREATE TABLE club (id integer primary key auto_increment, is_accessible char, manager_id integer NOT NULL) ENGINE=InnoDB;
ALTER TABLE club ADD CONSTRAINT manager_id_reference FOREIGN KEY(manager_id) REFERENCES manager (id);

Inserting some managers:
INSERT INTO manager(name) VALUES('Pritom');
INSERT INTO manager(name) VALUES('Kumar');

Inserting some clubs:
INSERT INTO club(is_accessible, manager_id) VALUES(true, 1);
INSERT INTO club(is_accessible, manager_id) VALUES(false, 2);

Let see manager table data (SELECT * FROM manager):

ID	NAME
1	Pritom
2	Kumar

Let see club table data (SELECT * FROM club):

ID	IS_ACCESSIBLE	MANAGER_ID
1	1	1
2	0	2

Let see manager table structure (DESCRIBE manager):

FIELD	TYPE	NULL	KEY	DEFAULT	EXTRA
id	int(11)	NO	PRI	(null)	auto_increment
name	text	YES		(null)

Let see club table structure (DESCRIBE club):

FIELD	TYPE	NULL	KEY	DEFAULT	EXTRA
id	int(11)	NO	PRI	(null)	auto_increment
is_accessible	char(1)	YES		(null)
manager_id	int(11)	NO	MUL	(null)

Now try to delete manager:
DELETE FROM USER WHERE id = 1;

will error as:
Schema Creation Failed: Cannot delete or update a parent row: a foreign key constraint fails (`db_2_8d4a1`.`club`, CONSTRAINT `manager_id_reference` FOREIGN KEY (`manager_id`) REFERENCES `manager` (`id`)):

This happens because the data in the manager table depends on the data in the club table.

Full sqlFiddle

Grails GORM creating foreign key on runtime

GORM mappings let you configure pretty much anything you need in your Grails applications, but occasionally there are more obscure tweaks that aren’t directly supported, and in this case a custom Configuration class is often the solution.

By default Grails uses an instance of GrailsAnnotationConfiguration and the standard approach is to subclass that to retain its functionality and override the secondPassCompile() method.

Create this class (with an appropriate name and package for your application) in src/java or src/groovy:

package com.pritom

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
import org.hibernate.mapping.Column
import org.hibernate.mapping.ForeignKey
import org.hibernate.mapping.PersistentClass

/**
 * Created with IntelliJ IDEA.
 * User: pritom
 * Date: 21/10/13
 * Time: 10:41 AM
 * To change this template use File | Settings | File Templates.
 */
class GormConfiguration extends GrailsAnnotationConfiguration {
    boolean renamedForeignKeys = false

    @Override
    protected void secondPassCompile() {
        super.secondPassCompile()

        if (renamedForeignKeys) {
            return
        }

        renameForeignKeys()
        renamedForeignKeys = true
    }

    void renameForeignKeys() {
        classes.values().each { PersistentClass persistentClass ->
            /**
             * There must be a domain name 'SportUser' which has a field 'user_id'.
             */
            if(persistentClass.entityName.equalsIgnoreCase("com.pritom.SportUser")) {
                persistentClass.table.columns.each {
                    /**
                     * user_id: Column name to behave as a foreign key
                     * There must be a domain name 'User' to linked with it.
                     */
                    if(it.value.name.equalsIgnoreCase("user_id")) {
                        Column column = (Column) it.value;
                        /**
                         * ACL_USER: Foreign key constraints name
                         * com.pritom.User: Domain to make the foreign object
                         */
                        persistentClass.table.createForeignKey("ACL_USER", [column], "com.pritom.User")
                    }
                }
            }
        }
    }

    String createHumanReadableName(ForeignKey key) {
        "${key.columns.first().name}__${key.referencedTable.name}_fkey"
    }
}

What is left is to activate the code which is a one liner, we just have to add a configClass parameter to the datasource configuration:

    dataSource {
        configClass = com.pritom.GormConfiguration
        driverClassName = "com.mysql.jdbc.Driver"
        dbCreate = "create"
        url = "jdbc:mysql://localhost/grails_db"
        username = "grails"
        password = "grails"
    }  

Grails: renaming foreign keys constraints

In Grails if we create a domain class with association we will get a foreign key for the association. Let’s see how the foreign key gets named using an example using a many-to-one association.
Here is a simple domain class with an association to itself.

package gorm

class Category {
    Category child
}

The create table for this class will look like this:

CREATE TABLE category (
    id bigint NOT NULL,
    version bigint NOT NULL,
    child_id bigint,
    CONSTRAINT category_pkey PRIMARY KEY (id),
    CONSTRAINT fk302bcfeabee40a7 FOREIGN KEY (child_id)
        REFERENCES category (id)
)

We can see that the primary key gets a human readable name category_pkey but the name of the foreign key is fk302bcfeabee40a7. Not too readable. It would be nice if we could give the foreign key a human readable name too. What about <COLUMN>_<TARGET_TABLE>_fkey to make it similar to the name of the primary key?
It looks like Grails does not offer any GORM DSL sugar to customize the name of the foreign key. But naming it manually on each domain class may be a bit cumbersome anyway. It would be nice if we could make it use the <COLUMN>__<TARGET_TABLE>_fkey format automatically.

Here is a version in groovy implementing the format given above:

package com.pritom

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
import org.hibernate.mapping.PersistentClass
import org.hibernate.mapping.ForeignKey

/**
 * Created with IntelliJ IDEA.
 * User: pritom
 * Date: 21/10/13
 * Time: 10:41 AM
 * To change this template use File | Settings | File Templates.
 */
class GormConfiguration extends GrailsAnnotationConfiguration {
    boolean renamedForeignKeys = false

    @Override
    protected void secondPassCompile() {
        super.secondPassCompile()

        if (renamedForeignKeys) {
            return
        }

        renameForeignKeys()
        renamedForeignKeys = true
    }

    void renameForeignKeys() {
        classes.values().each { PersistentClass persistentClass ->
            persistentClass.table.foreignKeyIterator.each { ForeignKey key ->
                key.name = createHumanReadableName(key)
            }
        }
    }

    String createHumanReadableName(ForeignKey key) {
        "${key.columns.first().name}__${key.referencedTable.name}_fkey"
    }
}

It is not perfect. We do not check the length of the generated name and there are are probably other details I do not know anything about yet we have to take care of. But it it a start. :-)
The create table now look like this:

CREATE TABLE category (
    id bigint NOT NULL,
    version bigint NOT NULL,
    child_id bigint,
    CONSTRAINT category_pkey PRIMARY KEY (id),
    CONSTRAINT child_id__category_fkey FOREIGN KEY (child_id)
        REFERENCES category (id)
)

With a nicely named foreign key. :-)
What is left is to activate the code which is a one liner, we just have to add a configClass parameter to the datasource configuration:

    dataSource {
        configClass = com.pritom.GormConfiguration
        driverClassName = "com.mysql.jdbc.Driver"
        dbCreate = "create"
        url = "jdbc:mysql://localhost/grails_db"
        username = "grails"
        password = "grails"
    }  

http://softnoise.wordpress.com/2012/08/26/grails-named-foreign-keys/