This plugin adds support for Foreign Key Constraints in schema dumper.
After adding this plugin, do a rake db_schema_dump and you’ll see statements for adding constraints in your schema.rb.
(Works with MySQL, and PostgreSQL)

You can use this plugin for your migrations as well. The syntax for adding FK constraints is:

add_foreign_key_constraint table, foreign_key, reference_table, reference_column, :name => optional_constraint_name, :on_update => optional_on_update_action, :on_delete => optional_on_delete_action

The actions are symbols. One of:
:cascade, :set_null, or :restrict
If you don’t want any action, “no action”, then just omit these optional parameters.

For removing:

You have two options:

1. :name => the name of the foreign key constraint
2. :foreign_key => the name of the column for which the foreign key was created (only if the default constraint_name was used)

Note: This is for inside ActiveRecord::Schema not inside migrations. You can use remove_foreign_key_constraint but it takes just the table name and constraint name

For example:

remove_foreign_key_constraint 'pictures', :foreign_key => 'user_id'

Download

http://www.bubbleshare.com/tools/ActiveRecordExtensions.zip

We are looking to submit this as a patch as well.

Can someone post a link to a subversion repository for this? It’s very useful and would be moreso if we could install it via ./script/plugin…

See also Plugins

Can anybody get this to work? How?
After adding this plugin and running `rake db_schema_dump` I see no add_foreign_key_constraint calls (just add_index calls).

Here’s what worked for me: Installed plugin into my running Rails application. Created a migration to create a new table with a foreign key constraint. `rake db_schema_dump` – I see both add_index and add_foreign_key_constraint.

Here’s what worked for me: Try using ‘rake db:schema:dump’, ‘rake db:migrate’ and so on instead of the underscore versions.

• svn://caboo.se/plugins/atmos/activerecord_foreign_key_extensions works with oracle too. Bubbleshare never got back to me.
• I have a patch that adds full support for PostgreSQL foreign keys there : : http://mat.cc/distfiles/activerecord_foreign_key_extensions-2006-09-07.diff

The Regex doesn’t match all postgres foreign keys

The following regex:

/FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\) ON UPDATE (\w+) ON DELETE (\w+)/

doesn’t match any of the following (valid) foreign key definition in postgres:

FOREIGN KEY (worker_type_id) REFERENCES worker_type(worker_type_id)

FOREIGN KEY (person_id) REFERENCES person(person_id) ON UPDATE CASCADE

To fix this, I added a big ugly if statment that I will DRY up soon, but for now it does the trick:

          if row[1] =~ /FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\) ON UPDATE (\w+) ON DELETE (\w+)/
            keys << ForeignKeyConstraintDefinition.new(row[0],
                                                       Regexp.last_match(1),
                                                       Regexp.last_match(2),
                                                       Regexp.last_match(3),
                                                       symbolize_foreign_key_constraint_action(Regexp.last_match(4)),
                                                       symbolize_foreign_key_constraint_action(Regexp.last_match(5)))
          elsif row[1] =~ /FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\) ON UPDATE (\w+)/
            keys << ForeignKeyConstraintDefinition.new(row[0],
                                                       Regexp.last_match(1),
                                                       Regexp.last_match(2),
                                                       Regexp.last_match(3),
                                                       symbolize_foreign_key_constraint_action(Regexp.last_match(4)),
                                                       symbolize_foreign_key_constraint_action('NO ACTION'))
          elsif row[1] =~ /FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\) ON DELETE (\w+)/
            keys << ForeignKeyConstraintDefinition.new(row[0],
                                                       Regexp.last_match(1),
                                                       Regexp.last_match(2),
                                                       Regexp.last_match(3),
                                                       symbolize_foreign_key_constraint_action('NO ACTION'),
                                                       symbolize_foreign_key_constraint_action(Regexp.last_match(4)))
          elsif row[1] =~ /FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\)/
            keys << ForeignKeyConstraintDefinition.new(row[0],
                                                       Regexp.last_match(1),
                                                       Regexp.last_match(2),
                                                       Regexp.last_match(3),
                                                       symbolize_foreign_key_constraint_action('NO ACTION'),
                                                       symbolize_foreign_key_constraint_action('NO ACTION'))
          end

Single regexp such as
/FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\) (MATCH (\w+))? ?(ON UPDATE (\w+))? ?(ON DELETE (\w+))?/ should work as well, and the matches will work respectively. 1 = foreign key, 2 = reftable, 3 = refcolumn, 4 = match statement (or nil if not present), 5,6 = on update (or nil if not present), 7,8 = on delete (or nil if not present).

This will also not match ON UPDATE/DELETE SET NULL, and it will fail when MATCH keyword is present in the foreign key.

Note: there’s a bug with postgres where it blows up if you have two schemas with the same table name. For example, if you have foo.bar and public.bar it will blow up here:
PGError: ERROR: more than one row returned by a subquery used as an expression : SELECT conname, pg_catalog.pg_get_constraintdef(oid) AS consrc FROM pg_catalog.pg_constraint WHERE contype='f' AND conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='bar')
To solve this problem, just replace line 36 and line 37 of “active_record_extensions.rb” with the following code:

        sql =  "SELECT f.conname, pg_get_constraintdef(f.oid) AS consrc FROM pg_class t, pg_constraint f " 
        sql += "WHERE f.conrelid = t.oid AND f.contype = 'f' AND t.relname = '#{table}'" 

I’m getting an error when trying to do a schema dump after installing this plugin: rake db:schema:dump—trace
(in /Volumes/personal/dev/astracorp/ems/prevpays/bugfix/pp)

• Invoke db:schema:dump (first_time)
• Invoke environment (first_time)
• Execute environment
  rake aborted!
  undefined method `default_value’ for class `ActiveRecord::ConnectionAdapters::PostgreSQLAdapter’
  /Volumes/personal/dev/astracorp/ems/prevpays/bugfix/pp/vendor/plugins/activerecord_foreign_key_extensions/lib/active_record_extensions.rb:56
  ...