Migration issues

[Omri]

Hi All,

Is there any documentation as for how I can define composite key in
the migration of the table?
more specifically, I try to do this:

class CreateUserBadges < ActiveRecord::Migration
def change
create_table :user_badges, :primary_key =>
[:user_id, :badge_id] do |t|
t.column :user_id, :integer
t.column :badge_id, :integer
t.timestamps
end
end
end

after running db migrate, my table (in MySql) looks like this:

CREATE TABLE `user_badges` (
`user_idbadge_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`badge_id` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`user_idbadge_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I am using rails 3.1.1, and composite_keys 4.0 ..

Any help would be appreciated,
Thanks

[Lori M Olson]

In general, composite primary keys are considered ... not an optimal
database design. Most people using the composite primary key gem do
so because they are dealing with legacy databases. Not many people
are creating new tables with composite primary keys.

If it is necessary, you can do it, but I would not recommend it.

[cfis]

Yeah, I have to disagree with Lori. Primary keys are fine, just
because Rails doesn't like them doesn't like them doesn't mean they
aren't a good idea.

As for migrations, I don't actually know since I never that style of
migrations. Happy to take a patch if the answer is now.

Charlie

[Sal Gonzalez]

Omri,

I was able to get composite migrations by hunting down a copy of the
composite_migrations code... It wasn't easy to find, most references
were 404'd. This was the best I could get:

http://asyrinx.googlecode.com/svn/trunk/rails/examples/cpk_sample/vendor/plugins/

You need to download that entire directory structure, and place it
in your vendor/plugins folder, add a "require 'composite_migrations'"
to the start of your migration source, then your create_table line
should work. This worked for me on rails 3.0.11, YMMV on newer
releases.

create_table :claims, :primary_key => [:ss_id, :claimnum_id] do |t|

[Sal Gonzalez]

One last thing, you have to edit the composite_migrations.rb file and
change the 'type_to_sql' call to be 'base.type_to_sql'

On Dec 30, 11:42 am, Sal Gonzalez <sgonza...@codejunkie.net> wrote:
> Omri,
>
>   I was able to get composite migrations by hunting down a copy of the
> composite_migrations code...  It wasn't easy to find, most references
> were 404'd.  This was the best I could get:
>

> http://asyrinx.googlecode.com/svn/trunk/rails/examples/cpk_sample/ven...

[Tyler Rick]

Hi Omri,

This is what I used for my migrations...

In a file lib/active_record/connection_adapters/table/add_primary_key.rb, I have:

ActiveRecord::ConnectionAdapters::Table

# Rails doesn't make it easy to add a composite primary key in a migration.

# This was written to make it as easy as writing this in your migration:

#   t.add_primary_key [:user_id, :other_id]

class ActiveRecord::ConnectionAdapters::Table

  module AddPrimaryKey

    def add_primary_key(columns)

      column_exists?(:id, nil, {})

      remove :id if column_exists?(:id, nil, {})

      @base.execute "alter table #{@table_name} add primary key(#{Array(columns).join(', ')})"

    end

    def drop_primary_key

      @base.execute "alter table  #{@table_name} drop primary key"

    end

    def undo_add_primary_key(columns)

      drop_primary_key

      Array(columns).each do |c|

        change c, :string, :null => true, :default => nil

      end

      add_default_primary_key

    end

    def add_default_primary_key

      integer :id unless column_exists?(:id, nil, {})

      change :id, :primary_key

    end

  end

end

class ActiveRecord::ConnectionAdapters::Table

  include AddPrimaryKey

end

Like the comment says, you can use it like this:

  t.add_primary_key [:user_id, :other_id]

[Tyler Rick]

I tried to post a reply but immediately after posting, it showed this where my new message would have been...

This message has been deleted.

[Derrell Durrett]

Tyler:

I'm just curious what you may have failed to post. The add_primary_key code is there.

Have you continued to have success with this approach?

Thanks,

Derrell