Adding primary_key to join table using migrations


#1

Hi list,

My tag_question_user join table is not currently a full-blown model. It
doesn’t have a primary key - just tag_id, question_id, and user_id.

I now want to add a primary key to this model (because I want to add a
“description” column as well so users can describe the tag.

I’m not sure how to add this using migrations.

Here is what I have:
class AddNotesToTags < ActiveRecord::Migration
def self.up
add_column :tags_questions_users, “id”, :primary_key
add_column :tags_questions_users, “description”, :string, :limit
=>
80, :null => false
end

def self.down
remove_column :tags_questions_users, “id”
remove_column :tags_questions_users, “description”
end
end

That’s giving me this error:

Mysql::Error: #42000You have an error in your SQL syntax; check the
manual
that
corresponds to your MySQL server version for the right syntax to use
near ‘’
at
line 1: ALTER TABLE tags_questions_users ADD id

Any suggestions?

Thanks,

Steve
http://www.smarkets.net


#2

Hi Steve,

Did you ever find a solution to this? We’re having the same problem.
As far as I can see the mysql adapter isn’t set up to handle the
:primary_key type.

I found a patch for Postgre:

http://dev.rubyonrails.org/ticket/3735

Looks like this might be fixed in 1.2

CHEERS> SAM


#3

I’m also looking for some way to drop a primary key, but my first
attempt:

remove_index :workspaces_discussions, :primary_key

has failed miserably:

Mysql::Error: Can’t DROP ‘workspaces_discussions_primary_key_index’;
check that
column/key exists: DROP INDEX workspaces_discussions_primary_key_index
ON worksp
aces_discussions

Many thanks for any suggestions.

CHEERS> SAM


#4

My table primary key is “DATE” and not “ID”

now When I use mymodel.find(“mydate”) it’s returns that my table does
not have a column “id”
ok…

now how to search by DATE and not “id” ?

tks.


#5

Hi Fernando,

Fernando wrote:

My table primary key is “DATE” and not “ID”

now When I use mymodel.find(“mydate”) it’s returns that my table does not
have a column “id”
ok…

now how to search by DATE and not “id” ?

Rails is built on top of ActiveRecord which assumes the primary key is a
“meaningless” integer. Simply add a date field (I don’t think you can
name
it just ‘date’, but you should check the wiki for reserved / magic
words).
In Rails, assuming your date column is named ‘mydate’, you’ll use

mymodel.find(:first, :conditions => [“mydate = ?”, mydate])

hth,
Bill


#6

On 7/6/06, Fernando removed_email_address@domain.invalid wrote:

My table primary key is “DATE” and not “ID”

now When I use mymodel.find(“mydate”) it’s returns that my table does
not have a column “id”
ok…

now how to search by DATE and not “id” ?

You need to override AR default with set_primary_key ‘date’ in your
Model. AR assumes the primary key is called “id”.
If you are not using legacy schemas, best practice is however using
Integer primary keys.
Cheers.