Foreign key plugin

[M. Barbosa]

Hi guys, i have just installed the foreign key pluging from http://
www.redhillconsulting.com.au/
rails_plugins.html#foreign_key_migrations.

The problem that I'm facing is the following: I have set up migrations
for 7 tables in a MySQL database. However, when I try to execute rake
db:migrate i get the following error:

== CreateInterests: migrating
=================================================
-- create_table(:interests)
rake aborted!
Mysql::Error: #HY000Can't create table './qinqy_development/
interests.frm' (errno: 150): CREATE TABLE interests (`id` int(11)
DEFAULT NULL auto_increment PRIMARY KEY, `location_id` int(11) NOT
NULL, `interest_type_id` int(11) NOT NULL, FOREIGN KEY (location_id)
REFERENCES locations (id), FOREIGN KEY (interest_type_id) REFERENCES
interest_types (id)) ENGINE=InnoDB

The migration code for the interests table is as follows:

class CreateInterests < ActiveRecord::Migration
def self.up
create_table :interests do |t|
t.column :location_id, :integer, :null => false
t.column :interest_type_id, :integer, :null => false
end
end

def self.down
drop_table :interests
end
end

So there are 2 foreign keys in this interests table, referring to
location_id and interest_type_id. According to the foreign key
migrations plugin, this code should be working. However, I keep
getting this error which prevents the migrations to take place. What
am I doing wrong?

[Zack Chandler]

Are you sure there exists in the DB a locations table with an id field
and an interest_types table with an id column?

--
Zack Chandler
http://depixelate.com

[Michel B]

The locations and interest_types migration files don't explicitly
define the id field. If I'm not mistaken, you don't have to specifcy
the id field.
Or will the problem be solved if the interest_types en locations table
ALREADY exist, before I apply any foreign keys?

[Xavier Noria]

Yes. Its create_table must come before, because the constraint needs
to refer to some column known by the database.

When you use that plugin the order of (create|drop)_table matters.

-- fxn

[Michel B]

The migration for interest_types looks like this:

class CreateInterests < ActiveRecord::Migration
def self.up
create_table :interests do |t|
t.column :location_id, :integer, :null => false
t.column :interest_type_id, :integer, :null => false
end
end

def self.down
drop_table :interests
end
end

I have deleted the other 6 migrations. If I perform rake db:migrate I
still get this error:

== CreateInterests: migrating
=================================================
-- create_table(:interests)
rake aborted!
Mysql::Error: #HY000Can't create table './qinqy_development/
interests.frm' (errno: 150): CREATE TABLE interests (`id` int(11)
DEFAULT NULL auto_increment PRIMARY KEY, `location_id` int(11) NOT
NULL, `interest_type_id` int(11) NOT NULL, FOREIGN KEY (location_id)
REFERENCES locations (id), FOREIGN KEY (interest_type_id) REFERENCES
interest_types (id)) ENGINE=InnoDB

How do I use this foreign key plugin? Do I have to use it in this way?

class CreateInterests < ActiveRecord::Migration
def self.up
create_table :interests do |t|
t.column :location_id, :integer, :null =>

false, :references => nil

t.column :interest_type_id, :integer, :null =>

false, :references => nil
end
end

def self.down
drop_table :interests
end
end

Now there are no foreign keys, but the table will be created
successfully. How do I go about creating the foreign key after this?

[Xavier Noria]

On Feb 8, 2007, at 1:14 PM, Michel B wrote:

>
> The migration for interest_types looks like this:
>
> class CreateInterests < ActiveRecord::Migration
> def self.up
> create_table :interests do |t|
> t.column :location_id, :integer, :null => false
> t.column :interest_type_id, :integer, :null => false
> end
> end
>
> def self.down
> drop_table :interests
> end
> end

*Before* that migration runs, tables "locations" and "interest_types"
must have been created, possibly in earlier migrations.

-- fxn