matthuhiggins / foreigner Goto Github PK

The foreigner doesn't work with mysql2 gem.

When running migrations the output says the following but the fk is not added to database.
== AddFkToProfiles: migrating ========
-- add_foreign_key(:profiles, :users)
-> 0.0001s
== AddFkToProfiles: migrated (0.0001s) ==

Could the support be added to this end or should it be added to mysql2?

This may or may not be a problem that Foreigner can solve, but I'm new to the gem and to Postgres itself and am hoping you can provide some insight.

Adding foreign key constraints breaks my test suite because during fixture loading Rails will disable and re-enable the constraints and the following error is raised:

ActiveRecord::StatementInvalid: PGError: ERROR:  permission denied: "RI_ConstraintTrigger_30381" is a system trigger

I've executed the exact statement as a superuser and as the regular user (which owns the test db, btw) and confirmed that the superuser can execute it just fine, but my regular user cannot. The sql in question is:

ALTER TABLE "registrations" ENABLE TRIGGER ALL;

Why are the foreign key constraints that are created by a user which owns the database be owned by the system and not ALTERable by said user?

(Rails 3.0.7, Foreigner 1.0.3, Ruby 1.9.2, Postgres 9.0.3)

I just realized that a bundle update at some point had moved me to a version of this that doesn't support the old mysql adapter. So for the last few months it has been silently not adding foreign keys during my migrations. It would be nice if there was a warning or some indication that I have an unsupported adapter.

I'm an idiot, so I can't spell. So when I misspell dependent, I'd like to be told. This is the rails behaviour, so it makes sense to me to be the same in foreigner (a great library, by the way, thanks to all).

The following generates no complaints, for instance.
add_foreign_key :a, :b, :nada => :delete

Thanks,
ben

In a basic situation where we have a foreign key with custom name, the foreign key remover tries to use the default naming for the foreign key.
Reproducable in PostgreSQL using this kind of syntax:
add_foreign_key(:groups, :members, :column => :participant_id)
remove_foreign_key(:groups, :members)

PGError: ERROR: constraint "members_group_id_fk" of relation "members" does not exist
: ALTER TABLE "members" DROP CONSTRAINT "members_group_id_fk"

Fail. Acceptable solution would be that remove_foreign_key receives the column name if that would help to remove the actual foreign_key. This is possible with current behavior if the column is passed as a hash to remove_foreign_key. A notion of this in the documentation would be nice to the developers, though. :)

When dumping foreign keys, it would be nice if we could skip including the constraint name if that name is auto-generated by the database. Example:

add_foreign_key "accounts", "account_types", :name => "accounts_ibfk_2"

For MySQL InnoDB, we should ignore /_ibfk_\d+$/.

Hello, I would like to use foreigner with jruby, connecting to the database via jdbc. I was able to get it to work with one tweak and a new adapter file (please see http://pastebin.com/f3ff2f0f8), but being something of a new ruby programmer I imagine this isn't very good, I don't like having to declare a fake ActiveRecord::ConnectionAdapters:: PostgreSQLAdapter just so I can require the postgresql_adapter.rb file. Maybe the top part of postgresql_adapter.rb can be factored out from the bottom. Anyways, I'm hoping you see some value to this and would consider merging it or a better version in. Tyvm.

We're using SQLite in :test and PostgreSQL in :development and :production. But foreigner depends on MySQL (which in turn depends on libmysql; can that dependency be removed the same way Rails was?

Change:
config.gem "matthuhiggins-foreigner", :lib => "foreigner", :source => http://gemcutter.org
to:
config.gem "matthuhiggins-foreigner", :lib => "foreigner", :source => "http://gemcutter.org"

Adding FKs to InnoDB tables creates a constraint and an index. On MyISAM tables only the index will be created. When the FK is removed these indexes are left behind since remove_fk() only drops the constraint.

I've written this fix: http://github.com/sshaw/foreigner/blob/master/lib/foreigner/connection_adapters/mysql_adapter.rb

This is arguably somewhat DB specific, which I know is something you're trying to avoid. SQL Server does not behave like MySQL (at least 2000), I don't know how Postgre handles FKs. Though the gem does rely on the transparency of engines supporting SQL standard syntax without actually implementing the given DDL commands so maybe it's OK.

I added the option :keep_index to avoid dropping indexes that existed before the constraint was added.

I also modified the arguments so one isn't forced to add the column name to the Hash
when specifying options, the Symbol can still be used.

Since the existing test case is broken I create an ad hoc one.

I am using Rails 3 and have in my gemfile

gem 'matthuhiggins-foreigner', :require => 'foreigner'
gem 'gravtastic', '~> 2.2.0'

Then when I start my server, I get this error:

$ rails s
/Library/Ruby/Gems/1.8/gems/matthuhiggins-foreigner-0.6.4/lib/foreigner.rb:29:in `on_load': undefined method `config' for nil:NilClass (NoMethodError)
from /Library/Ruby/Gems/1.8/gems/activesupport-3.0.0/lib/active_support/lazy_load_hooks.rb:36:in `instance_eval'
from /Library/Ruby/Gems/1.8/gems/activesupport-3.0.0/lib/active_support/lazy_load_hooks.rb:36:in `execute_hook'
from /Library/Ruby/Gems/1.8/gems/activesupport-3.0.0/lib/active_support/lazy_load_hooks.rb:43:in `run_load_hooks'
from /Library/Ruby/Gems/1.8/gems/activesupport-3.0.0/lib/active_support/lazy_load_hooks.rb:42:in `each'
from /Library/Ruby/Gems/1.8/gems/activesupport-3.0.0/lib/active_support/lazy_load_hooks.rb:42:in `run_load_hooks'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0/lib/active_record/base.rb:1867
from /Library/Ruby/Gems/1.8/gems/gravtastic-2.2.0/lib/gravtastic.rb:89
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:64:in `require'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:64:in `require'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:62:in `each'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:62:in `require'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:51:in `each'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler/runtime.rb:51:in `require'
from /Library/Ruby/Gems/1.8/gems/bundler-1.0.0/lib/bundler.rb:112:in `require'
from /Users/chad/Dropbox/Sites/pc/config/application.rb:7
from /Library/Ruby/Gems/1.8/gems/railties-3.0.0/lib/rails/commands.rb:28:in `require'
from /Library/Ruby/Gems/1.8/gems/railties-3.0.0/lib/rails/commands.rb:28
from /Library/Ruby/Gems/1.8/gems/railties-3.0.0/lib/rails/commands.rb:27:in `tap'
from /Library/Ruby/Gems/1.8/gems/railties-3.0.0/lib/rails/commands.rb:27
from script/rails:6:in `require'
from script/rails:6

The offending line in gravtastic-2.2.0/lib/gravtastic.rb is

ActiveRecord::Base.send(:include, Gravtastic) if defined?(ActiveRecord) # :nodoc:

And in Foreigner is

ActiveRecord::Base.configurations = Rails.application.config.database_configuration

I don't know if Gravtastic or Foreigner is to blame here; if I comment out either from my Gemfile, the server starts up just fine. You can go over to chris's gravtastic to see the similar post that I left there.

I'd happily help fix it, but I'm new to this whole world of rails and I've no experience with writing/fixing gems, so I may need some guidance.

I’ve been having problems with schema load failing as a result of the add foreign key statements that are added by foreigner, migrations work fine but it causes my tests to fail. I’m getting mysql error no 150 could not create table - don’t know if this is just a mysql problem, and maybe a result of the foreign keys names that we’re using or.... has anyone had issues with this? At the moment I am just commenting out those lines and things run smoothly.

Foreigner should support foreign keys for sqlite3 builds that support them: http://www.sqlite.org/foreignkeys.html

It seems that when you are using the in-line way of defining foreign keys in the field definition when you create a table there's no way to define the name of the other table. I couldn't find it on the documentation and a quick look at the code told me it was impossible (I might be wrong).

For example, I have:

create_table :items do |t|
  #...
  t.references :recommender, :foreign_key => {:dependent => :nullify}
  #...
end

There's no recommenders table, recommender_id should point to the users table. What I'm trying to do is this (which works but is more verbose):

create_table :items do |t|
  #...
  t.references :recommender, :null => true
  t.foreign_key :users, :column => :recommender_id, :dependent => :nullify
  # ...
end

Looks like same problem mentioned here: http://github.com/matthuhiggins/foreigner/issues/closed#issue/13

 /app/vendor/bundler_gems/gems/foreigner-0.9.0/lib/foreigner.rb:32:in `configured_adapter':NoMethodError: undefined method `spec' for nil:NilClass

I think the load_adapter! call is happening too early. Obviously for 2.x we're skipping the Railtie block and immediately loading the adapter. Perhaps the problem is that I'm using bundler and not config.gem so the gem is being required before rails is fully initialized.

Manually calling Foreigner.load_adapter! in a config.after_initialize block in the environment.rb works fine provided I force the gem to only call that method if ActiveRecord::Base.connection_pool is available.

Quick fix would be to add this to the Rails 2 loading of foreigner and leaving it up to the developers to manually call this after_initialize unless you know of a way to set it up to happen after initialize once we know the pool isn't available.

 Foreigner.load_adapter! if ActiveRecord::Base.connection_pool

Per the documentation, if one were to do the following:

create_table :comments do |t|
t.foreign_key :posts
t.references :user
end


Then the expectation is that the table 'comments' would have a field in it called 'user_id' and a foreign key would be created to 'users(id)'.

Instead, while the correct 'user_id' column is created, the foreign key that is created is for 'user(id)'.

I have a fix for this problem checked into my branch, but I warn that this fix causes 'add_foreign_key' to break when it wants to create a relationship to a singularly named table - and therefore, causes several of your unit tests to break.

I've checked my fix into my branch for now but I recommend not pulling this change, or pulling it and then immediately fixing your code to support both documented features.

http://github.com/goberoi/foreigner/tree/master

Hello matthuhiggins,

Can you publish the updated gem?

Gemcutter have the version 0.1.0 and the gemspec is defined as 0.4.0.

Regards

Hi,
I tried to add ":foreign_key => {:dependent => :delete}" to t.references but nothing change in my schema.rb file, I had no errors but also no foreign_keys in my mysql database, then I tried to add the functions add_foreign_key(from_table, to_table, options) and I find the dumped functions in my schema but I had this error while db:migration

undefined method `foreign_key' for #ActiveRecord::ConnectionAdapters::TableDefinition:0x00000003204c40

I'm using Rails 3 and Ruby 1.9.2

My migration file

create_table :subscriptions do |t|
  t.references :package, :foreign_key => {:dependent => :delete},  null:false
  t.references :user, :foreign_key => {:dependent => :delete},  null:false
  t.timestamps
end

add_index :subscriptions, :package_id
add_index :subscriptions, :user_id

thanks

this would be pretty useful, and (probably) not as hard as it sounds...

it's right along the lines of what i've done for triggers in hairtrigger. basically that gem lets you declare your database triggers right in your models, and you can use a rake task to generate the appropriate db-agnostic migration. it does this by:

1. loading all models and seeing what triggers they define
2. dry-running existing migrations to see what triggers are defined/update/removed
3. "diff"-ing the result, and then creating a new migration with the appropriate create_trigger/drop_trigger statements. this way it properly catches any and all changes to model triggers

doing the same thing for associations -> foreign keys seems pretty doable as well... infer the foreign keys from the associations (removing any duplicates), dry-run the migrations/schema.rb to see what foreign keys they define, do the diff, and create a new migration with the right add_foreign_key/remove_foreign_key calls.

there are some gotchas to consider, like ignoring/whitelisting foreign keys that were explicitly declared in a migration (not auto-generated), and providing ways for users to override stuff (if necessary), but all in all it could be a pretty killer feature... users would get magical foreign keys in rails without having to manually write any migrations.

I changed line 26 in foreigner.rb to:

  if defined?(Rails) && Rails.version >= '3.0'

Because I am using foreigner with ActiveRecord outside of Rails. Let me know what you think of this change.

This means that while you can do 'rake db:migrate' to setup a db, doing 'rake db:schema:load' or 'rake db:reset' will not get you the foreign-key constraints that you desire.

After fixing the other issue, I ran across another problem: the method 'references_without_foreign_key' is undefined. That method is called from schema_definitions.rb.

I tried to use foreigner with my application. I use the ActiveRecord capability to automatically prefix all table names with the value of config.active_record.table_name_prefix parameter.

I found that foreigner's add_foreign_key function got lost with this configuration. It produced wrong database query (I'm using MySQL):
ALTER TABLE prefix_language_names ADD CONSTRAINT prefix_language_names_language_id_fk FOREIGN KEY (language_id) REFERENCES languages(id)

instead of:

ALTER TABLE prefix_language_names ADD CONSTRAINT prefix_language_names_language_id_fk FOREIGN KEY (language_id) REFERENCES prefix_languages(id)

The problem was that the name of the target table was not prefixed by foreigner.

It will make foreigner more compliant with ActiveRecord if this feature works correctly.

A fresh Rails 3 application does not support both foreigner and cancan simultaneously. I think it is a foreigner issue:

$ rails c
/usr/lib/ruby/gems/1.8/gems/matthuhiggins-foreigner-0.6.4/lib/foreigner.rb:29:in on_load': undefined methodconfig' for nil:NilClass (NoMethodError)
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/lazy_load_hooks.rb:17:in instance_eval' from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/lazy_load_hooks.rb:17:inexecute_hook'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/lazy_load_hooks.rb:24:in run_load_hooks' from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/lazy_load_hooks.rb:23:ineach'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/lazy_load_hooks.rb:23:in run_load_hooks' from /usr/lib/ruby/gems/1.8/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:1803 from /usr/lib/ruby/gems/1.8/gems/cancan-1.3.3/lib/cancan/active_record_additions.rb:39 from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:219:inrequire'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:219:in require' from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:205:inload_dependency'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:570:in new_constants_in' from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:205:inload_dependency'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.0.0.rc/lib/active_support/dependencies.rb:219:in require' from /usr/lib/ruby/gems/1.8/gems/cancan-1.3.3/lib/cancan.rb:5 from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:64:inrequire'
from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:64:in require' from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:62:ineach'
from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:62:in require' from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:51:ineach'
from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler/runtime.rb:51:in require' from /usr/lib/ruby/gems/1.8/gems/bundler-1.0.0.rc.5/lib/bundler.rb:107:inrequire'
from /tmp/ble/config/application.rb:7
from /usr/lib/ruby/gems/1.8/gems/railties-3.0.0.rc/lib/rails/commands.rb:21:in require' from /usr/lib/ruby/gems/1.8/gems/railties-3.0.0.rc/lib/rails/commands.rb:21 from script/rails:6:inrequire'
from script/rails:6

Fix for issue #46 (pull request #47) breaks commit 5bceeb3, introduced to correctly dump foreign keys from postgres databases when multiple schemas are used. The fix causes the dump of all existent foreign keys no matter the schemas set in the path.

Only current schemas should be considered, the same way tables method in the postgres adapter of Active Record does. See activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb.

I'll create a fix for this issue, taking into account the new SQL statement introduced in #47.

I am getting this error:

/home/warden/.rvm/gems/ree-1.8.7-2011.03@karierait-mod-testow/gems/foreigner-1.0.1/lib/foreigner/connection_adapters/mysql2_adapter.rb:4: /home/warden/.rvm/gems/ree-1.8.7-2011.03@karierait-mod-testow/gems/foreigner-1.0.1/lib/foreigner/connection_adapters/sql2003.rb:29: syntax error, unexpected ')', expecting '=' (SyntaxError)
/home/warden/.rvm/gems/ree-1.8.7-2011.03@karierait-mod-testow/gems/foreigner-1.0.1/lib/foreigner/connection_adapters/sql2003.rb:62: syntax error, unexpected kEND, expecting $end
    from /home/warden/.rvm/gems/ree-1.8.7-2011.03@karierait-mod-testow/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:239:in `require'
    from /home/warden/.rvm/gems/ree-1.8.7-2011.03@karierait-mod-testow/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:239:in `require'

I have fixed it manually doing options = {}) instead of options) in that line....

using rubyEE.

Postgres understandably complains about the lack of DROP CASCADE when rake db:schema:load tries to drop a table, e.g.:

-- create_table("answers", {:force=>true})
rake aborted!
PGError: ERROR:  cannot drop table answers because other objects depend on it
DETAIL:  constraint comments_answer_id_fk on table comments depends on table answers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
: DROP TABLE "answers"

Hello,

I think it would be nice to be able to delete foreign keys in a change_table, the same way one can add a foreign key. I'm not sure if you can somehow extend the remove method, but even remove_foreign_key would be nice:

change_table :blahs do |t|
  t.remove_foreign_key :users
end

Thanks.

Indexes are displayed under the relevant table in schema.rb.

Foreign keys should act the same, instead of being displayed in a big block under the table definitions.

Column name is not pluralized, and arguments are handled incorrectly, causing an extra, empty, foreign key to be attempted to be created.

From b993a5843522e34f11f074c9069cd18d0d1c653b Mon Sep 17 00:00:00 2001
From: Henrik Steensland 
Date: Sun, 13 Dec 2009 11:07:00 +0100
Subject: [PATCH 2/2] Fixed "references" in change_table block

---
 .../abstract/schema_definitions.rb                 |    2 ++
 1 files changed, 2 insertions(+), 0 deletions(-)

diff --git a/lib/foreigner/connection_adapters/abstract/schema_definitions.rb b/lib/foreigner/connection_adapters/abstract/schema_definitions.rb
index e417c5b..a02b659 100644
--- a/lib/foreigner/connection_adapters/abstract/schema_definitions.rb
+++ b/lib/foreigner/connection_adapters/abstract/schema_definitions.rb
@@ -110,6 +110,7 @@ module Foreigner
         # ====== Defining the column of the +to_table+.
         #  t.foreign_key(:people, :column => :sender_id, :primary_key => :person_id)
         def foreign_key(to_table, options = {})
+          to_table = to_table.to_s.pluralize if ActiveRecord::Base.pluralize_table_names
           @base.add_foreign_key(@table_name, to_table, options)
         end
     
@@ -143,6 +144,7 @@ module Foreigner
           fk_options = options.delete(:foreign_key)
 
           references_without_foreign_keys(*(args << options))
+          args.delete(options)
 
           if fk_options && !polymorphic
             fk_options = {} if fk_options == true
-- 
1.6.3.3

I get the following error:

undefined method `foreign_key' for #<ActiveRecord::ConnectionAdapters::TableDefinition:0x00000101616e38>

Looking at dwilkie's fork it seems he has a Foreigner::ConnectionAdapters::TableDefinition module as well as a Table module. Unfortunately his fork doesn't support mysql2 gem currently.

Hi,

I'm using this great plugin with both t.references :other, :foreign_key=>true and t.foreign_key :users, :column => "owner_id".
However, whenever I migrate I receive the following error message : undefined local variable or method 'to_table' for #<ActiveRecord::SchemaDumper:0xb76f7324> .
The resulting schema is then missing the final end.

Could this be coming from the line 26 of your schema_dumper.rb ?

if foreign_key.options[:column] != "#{to_table.singularize}_id"
    statement_parts << (':column => ' + foreign_key.options[:column].inspect)
end

Regards,

Creating reference in one step, with creating foreign_key constraint does not work.

• references_without_foreign_key expects name(s) which are element names, thus singular
• references_with_foreign_keys expect names(s) to be tables, thus plural.

Should change following line in schema_definitions.rb

args.each { |to_table| foreign_key(to_table, fk_options) }

to

args.each { |to_table| foreign_key(to_table.to_s.pluralize, fk_options) } 

Hi Matt,

I have some migrations that use create_table, and within them, t.references to create columns. Prior to installing foreigner, I was able to run all migrations without issue (rake db:migrate, or rake db:schema:load).

After installing foreigner, I get the following error when I run rake db:migrate on a fresh new db:

Without foreigner, rake db:migrate runs fine, and the statement above looks like:

You'll notice that the difference between the two is that with Foreigner installed, there is an extra comma after the 'version' field is described and that's what seems to be causing the issue.

(version varchar(255) NOT NULL, )

Note that the table above doesn't actually have any foreign keys (it's created by Rails), so the issue may have to do with how create_table works after Foreigner is installed.

Any ideas as to what might be causing this?

Cheers,
Gaurav

Hi,

In order to have a working postgres on current master, I needed to apply this patch :

diff --git a/src/vendor/plugins/foreigner/lib/foreigner/connection_adapters/postgresql_adapter.rb b/src/vendor/plugins/foreigner/lib/foreigner/connection_adapters/postgresql_adapter.rb
index be662b3..fec81cd 100644
--- a/src/vendor/plugins/foreigner/lib/foreigner/connection_adapters/postgresql_adapter.rb
+++ b/src/vendor/plugins/foreigner/lib/foreigner/connection_adapters/postgresql_adapter.rb
@@ -23,7 +23,7 @@ module Foreigner
         }
         
         fk_info.map do |row|
-          options = {:column => row['column'], :name => row['name'], :primary_key = row['primary_key']}
+          options = {:column => row['column'], :name => row['name'], :primary_key => row['primary_key']}
 
           if row['dependency'] == 'CASCADE'
             options[:dependent] = :delete
@@ -33,6 +33,17 @@ module Foreigner
           ForeignKeyDefinition.new(table_name, row['to_table'], options)
         end
       end
+
+      def remove_foreign_key(table, options)
+        if Hash === options
+          foreign_key_name = foreign_key_name(table, options[:column], options)
+        else
+          foreign_key_name = foreign_key_name(table, "#{options.to_s.singularize}_id")
+        end
+
+        execute "ALTER TABLE #{quote_table_name(table)} DROP CONSTRAINT #{quote_column_name(foreign_key_name)}"
+      end
+
     end
   end
 end

The README says that remove_foreign_key takes only a :from_table argument, but how can that be?

The inline documentation in abstract_adapter gives some more detailed examples, but the examples don't seem to match up with their explanations e.g.:

# ====== Remove the foreign key named accounts_branch_id_fk in the accounts table.
    # remove_foreign_key :column => :branch_id

Where does the "accounts" come from?

Hi,

This plugin is great, thanks. Here is a small contribution, because "t.foreign_key" and "add_foreign_key" behaves differently on :name option.
Here is a small patch which fixes this behaviour, in lib/foreigner/connection_adapters/sql_2003.rb :

@@ -4,25 +4,24 @@ module Foreigner
       def supports_foreign_keys?
         true
       end
-    
+
       def add_foreign_key(from_table, to_table, options = {})
         column  = options[:column] || "#{to_table.to_s.singularize}_id"
-        foreign_key_name = foreign_key_name(from_table, column, options)
 
-        sql =
-          "ALTER TABLE #{quote_table_name(from_table)} " +
-          "ADD CONSTRAINT #{quote_column_name(foreign_key_name)} " +
-          foreign_key_definition(to_table, options)
-      
+        sql = "ALTER TABLE #{quote_table_name(from_table)} ADD " <<
+          foreign_key_definition(from_table, to_table, options)
+
         execute(sql)
       end
-    
-      def foreign_key_definition(to_table, options = {})
+
+      def foreign_key_definition(from_table, to_table, options = {})
         column  = options[:column] || "#{to_table.to_s.singularize}_id"
+        foreign_key_name = foreign_key_name(from_table, column, options)
         primary_key = options[:primary_key] || "id"
         dependency = dependency_sql(options[:dependent])
 
-        sql = "FOREIGN KEY (#{quote_column_name(column)}) REFERENCES #{quote_table_name(to_table)}(#{primary_key})"
+        sql = "CONSTRAINT #{quote_column_name(foreign_key_name)} "
+        sql << "FOREIGN KEY (#{quote_column_name(column)}) REFERENCES #{quote_table_name(to_table)}(#{primary_key})"
         sql << " #{dependency}" unless dependency.blank?
         sql
       end

and for lib/foreigner/connection_adapters/abstract/schema_definitions.rb

     module TableDefinition
       class ForeignKey < Struct.new(:base, :to_table, :options)
         def to_sql
-          base.foreign_key_definition(to_table, options)
+          base.foreign_key_definition(@table_name, to_table, options)
         end
         alias to_s :to_sql
       end

Make this gem available on gemcutter (and update the readme) now that Github no longer builds gems.

I'm not sure why but it does't seem to be creating anything when I set it as this option. It does work when i call it separately with add_foreign_key however.

I looked at the code a bit and am wondering if its because this line in schema_definitions.rb:

alias_method_chain :references, :foreign_keys

should read

alias_method_chain :references, :references_with_foreign_keys

Hi,

I have the following code in my rails setup migration:

create_table :users do |t|
      t.string :login, :limit => 40
      t.string :identity_url      
      t.string :name, :limit => 100, :default => '', :null => true
      t.string :email, :limit => 100
      ...
      t.timestamps
 end

create_table :profiles do |t|
      t.references :user
      t.string :real_name
      t.string :location
      t.string :website
      t.timestamps
end

add_foreign_key(:profiles, :user, :dependent => :delete)

When I run the migration, it throws the following error:

[...]
-- add_foreign_key(:profiles, :user, {:dependent=>:delete})
rake aborted!
An error has occurred, all later migrations canceled:

Mysql::Error: Can't create table 'test.#sql-160c_3f' (errno: 150): ALTER TABLE `profiles` ADD CONSTRAINT `profiles_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(id) ON DELETE CASCADE

I'm new to Rails so I don't know if it's an issue or that I'm doing something wrong.

Btw, I'm using Rails 2.3.8, Ruby 1.8.7, MySQL 5.1.36, under Windows 7 x64

Hi there

I have the following code in my environment.rb file that for MSSQL forces the use of BIGINTs for primary keys:

module JdbcSpec
  module MsSQL
    def modify_types(tp)
      super(tp)
      tp[:primary_key] = "bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY"
      tp
    end
  end
end

Now if I add the following to my environment.rb file the above code no longer works:

config.gem "matthuhiggins-foreigner", :lib => "foreigner"

Any idea why that might be?

Thanks.

Since foreigner adds foreign keys without explicit naming (when used in a create_table references), the removal fails, since it's assumed that the key ends with _fk instead of _fkey

From 733ab26603596b8c02dad57d548ebe0d06c8d198 Mon Sep 17 00:00:00 2001
From: Henrik Steensland 
Date: Sun, 13 Dec 2009 11:03:52 +0100
Subject: [PATCH 1/2] Fixed problem removing foreign keys on postgres

Postgres uses _fkey instead of _fk at the end of
automatically named foreign keys.

---
 .../connection_adapters/postgresql_adapter.rb      |   10 ++++++++++
 1 files changed, 10 insertions(+), 0 deletions(-)

diff --git a/lib/foreigner/connection_adapters/postgresql_adapter.rb b/lib/foreigner/connection_adapters/postgresql_adapter.rb
index 23f13d9..3335678 100644
--- a/lib/foreigner/connection_adapters/postgresql_adapter.rb
+++ b/lib/foreigner/connection_adapters/postgresql_adapter.rb
@@ -43,6 +43,16 @@ module Foreigner
           ForeignKeyDefinition.new(table_name, row['to_table'], options)
         end
       end
+
+      private
+      def foreign_key_name(table, column, options = {})
+     if options[:name]
+         options[:name]
+     else
+         "#{table}_#{column}_fkey"
+     end
+      end
+
     end
   end
 end
-- 
1.6.3.3

If you add a references after the table was created and try to add a foreign key to it, it fails.

This code

add_column :items, :item_type, :references, :null => false, :default => 1
add_foreign_key :items, :item_types

gives me this error:

Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references DEFAULT 1 NOT NULL' at line 1: ALTER TABLE `items` ADD `item_type` references DEFAULT 1 NOT NULL

but this code works:

add_column :items, :item_type_id, :integer, :null => false, :default => 1
add_foreign_key :items, :item_types

I attempted to use the t.foreign_key create table notation as documented:

create_table :reserves do |t|
  t.foreign_key :points, :dependent => :nullify
end

No error occurs though the table does not have a point_id column. I added the point_id column and migrate:

create_table :reserves do |t|
  t.point_id
  t.foreign_key :points, :dependent => :nullify
end

Though the point_id column exists though no key or constraint is added. I added some debugging and determined that this form does not call into sql_2003.rb:add_foreign_key.

The long form works as expected.

create_table :reserves do |t|
  t.point_id
end
add_foreign_key(:reserves, :points, :dependent => :nullify)

...since this breaks rake db:create (rake loads AR which loads foreigner, which attempts to connect to the still-nonexistant db.)

Another way is to pluck the adapter name from the configured connection pool. See [1] for a patch

[1] http://github.com/seomoz/foreigner/commit/7152841246110317840b7387b33dd210c9545635

Also, great plugin!

Rails version: 3.0.0.beta3

When using foreigner with authlogic from "gem 'authlogic', :git => 'http://github.com/odorcicd/authlogic.git', :branch => 'rails3'" following error occurs:
foreigner.rb:29:in on_load': undefined methodconfig' for nil:NilClass (NoMethodError)
when trying to use generators, migrations, starting server.

Hello,

I have a postgresql database with 149 foreign keys (241 counting duplicates).

~/Code/... [master] [ruby-1.9.2-p180] 
17:14:51 $ cat db/schema.rb  | grep foreign_key | wc -l
     241

~/Code/... [master] [ruby-1.9.2-p180] 
17:14:58 $ cat db/schema.rb  | grep foreign_key | uniq | wc -l
     149

My schema.rb duplicates some foreign keys without reason and databases can't be created from schema.

This happens on rails 3 (3.0.0 and 3.0.5) and foreigner 0.9.1 (master have same issue).

A small piece of duplication:

add_foreign_key "inventory_request_reservation_items", "inventory_request_items", :name => "request_item_reservation_id_fk", :column => "request_item_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"

add_foreign_key "inventory_request_reservation_list_item_inventories", "inventories", :name => "inventory_id_fk", :column => "inventory_id"

add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservation_items", :name => "reservation_item_id_fk", :column => "reservation_item_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservation_lists", :name => "list_id_fk", :column => "list_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"
add_foreign_key "inventory_request_reservation_list_items", "inventory_request_reservations", :name => "reservation_id_fk", :column => "reservation_id"

Maybe the reason is the same name on different tables?

Bamboo stack.

Heroku thrown no errors. Logs state rake crashes but migration shows as up and complete.

Hi!

I'm using bundler to load gems in a project, but when I add foreigner I get the following error:

undefined method spec' for nil:NilClass app/vendor/bundled_gems/gems/matthuhiggins-foreigner-0.4.1/lib/foreigner.rb:17 app/vendor/bundled_gems/gems/matthuhiggins-foreigner-0.4.1/lib/foreigner.rb:16:inclass_eval'
app/vendor/bundled_gems/gems/matthuhiggins-foreigner-0.4.1/lib/foreigner.rb:16

This error doesn't happen if I add the gem to the environment.rb file

Two problems: one is outlined in issue #35 -Foreigner needs an active connection to run, and the MySQL module included in the test does not exist.