Foreign key plugin

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?

On 2/7/07, M. Barbosa [email protected] wrote:

=================================================
The migration code for the interests table is as follows:
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?

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 C.
http://depixelate.com

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

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?

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

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?

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

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

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?