Disabling foreign keys ?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Disabling foreign keys ?

Martin Saucier-2
Is there a way in Grails to tell Hibernate NOT to create foreign key constraints on association ? I know this would break the referential integrity at the DB level but we want to use DB partitioning in MySQL which doesn't allow foreign keys.

Thanks,

Martin Saucier
Solutions Architect
t 506.874.8906

 



Reply | Threaded
Open this post in threaded view
|

Re: Disabling foreign keys ?

webmediauk
Hey Martin,
As you say probably not advisable to disable the foreign key constraints created by GORM but I had a similar situation a few days ago and I ended up adding an SQL query to my BootStrap. This had the effect of dropping some constraints I didn't want.
Code below I don't advise using it as such but if you really need it:

// this is a hack
        try{
            def sql = Sql.newInstance(
ConfigurationHolder.config.dataSource.url, ConfigurationHolder.config.dataSource.username, ConfigurationHolder.config.dataSource.password, ConfigurationHolder.config.dataSource.driverClassName)
            sql.execute("ALTER TABLE blah DROP CONSTRAINT blah_pkey;")  
        }catch (Exception e){
            System.out.println e
        }

This works with PostgreSQL but you might need to look up the MySQL equivalent to drop constraints.
Thanks
Reply | Threaded
Open this post in threaded view
|

Re: Disabling foreign keys ?

Martin Saucier-2
I would like to try that but how did you get around the fact that the foreign key constraint names are auto-generated like ''FK24217FDE6FC78B41", is there a way to tell GORM to use specific (user defined) constraints name while using hasMany or BelongsTo mappings ?

Martin

On 2011-12-13, at 12:42 PM, webmediauk wrote:

Hey Martin,
As you say probably not advisable to disable the foreign key constraints
created by GORM but I had a similar situation a few days ago and I ended up
adding an SQL query to my BootStrap. This had the effect of dropping some
constraints I didn't want.
Code below I don't advise using it as such but if you really need it:

// this is a hack
       try{
           def sql = Sql.newInstance(
ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
           sql.execute("ALTER TABLE blah DROP CONSTRAINT blah_pkey;")   
       }catch (Exception e){
           System.out.println e
       }

This works with PostgreSQL but you might need to look up the MySQL
equivalent to drop constraints.
Thanks

--
View this message in context: http://grails.1312388.n4.nabble.com/Disabling-foreign-keys-tp4190983p4191068.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Martin Saucier
Solutions Architect
t 506.874.8906

 



Reply | Threaded
Open this post in threaded view
|

Re: Disabling foreign keys ?

Jason Davis
another hack for mysql should you be using it: disable foreign key checking :s

On Tue, Dec 13, 2011 at 10:12 AM, Martin Saucier <[hidden email]> wrote:
I would like to try that but how did you get around the fact that the foreign key constraint names are auto-generated like ''FK24217FDE6FC78B41", is there a way to tell GORM to use specific (user defined) constraints name while using hasMany or BelongsTo mappings ?

Martin

On 2011-12-13, at 12:42 PM, webmediauk wrote:

Hey Martin,
As you say probably not advisable to disable the foreign key constraints
created by GORM but I had a similar situation a few days ago and I ended up
adding an SQL query to my BootStrap. This had the effect of dropping some
constraints I didn't want.
Code below I don't advise using it as such but if you really need it:

// this is a hack
       try{
           def sql = Sql.newInstance(
ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
           sql.execute("ALTER TABLE blah DROP CONSTRAINT blah_pkey;")   
       }catch (Exception e){
           System.out.println e
       }

This works with PostgreSQL but you might need to look up the MySQL
equivalent to drop constraints.
Thanks

--
View this message in context: http://grails.1312388.n4.nabble.com/Disabling-foreign-keys-tp4190983p4191068.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Martin Saucier
Solutions Architect
t <a href="tel:506.874.8906" value="+15068748906" target="_blank">506.874.8906

 




Reply | Threaded
Open this post in threaded view
|

Re: Disabling foreign keys ?

robelsner
You can set the naming strategy.  See this example:


or Burt's excellent post here:


to see how you can set naming of foreign keys.

Rob
Reply | Threaded
Open this post in threaded view
|

Re: Disabling foreign keys ?

Dan Durkin
In reply to this post by Jason Davis
You could also consider using the database-migration plugin.

This will give you complete control over the schema generation.

On Tue, Dec 13, 2011 at 12:45 PM, Jason Davis <[hidden email]> wrote:
another hack for mysql should you be using it: disable foreign key checking :s


On Tue, Dec 13, 2011 at 10:12 AM, Martin Saucier <[hidden email]> wrote:
I would like to try that but how did you get around the fact that the foreign key constraint names are auto-generated like ''FK24217FDE6FC78B41", is there a way to tell GORM to use specific (user defined) constraints name while using hasMany or BelongsTo mappings ?

Martin

On 2011-12-13, at 12:42 PM, webmediauk wrote:

Hey Martin,
As you say probably not advisable to disable the foreign key constraints
created by GORM but I had a similar situation a few days ago and I ended up
adding an SQL query to my BootStrap. This had the effect of dropping some
constraints I didn't want.
Code below I don't advise using it as such but if you really need it:

// this is a hack
       try{
           def sql = Sql.newInstance(
ConfigurationHolder.config.dataSource.url,
ConfigurationHolder.config.dataSource.username,
ConfigurationHolder.config.dataSource.password,
ConfigurationHolder.config.dataSource.driverClassName)
           sql.execute("ALTER TABLE blah DROP CONSTRAINT blah_pkey;")   
       }catch (Exception e){
           System.out.println e
       }

This works with PostgreSQL but you might need to look up the MySQL
equivalent to drop constraints.
Thanks

--
View this message in context: http://grails.1312388.n4.nabble.com/Disabling-foreign-keys-tp4190983p4191068.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Martin Saucier
Solutions Architect
t <a href="tel:506.874.8906" value="+15068748906" target="_blank">506.874.8906