Migrations: Manually create primary key (MySQL)

I need to use a UUID as a primary key.

I have everything working right now with the exception of being able
to create the column in MySQL (v4) as a primary key.

The migration looks like this:

create_table :groups, :id => false do |t|
      t.string :id, :limit => 36, :null => false
      t.string :name, :limit => 100, :null => false
      t.string :description, :limit => 500
      t.timestamps
end

All I want to do is specify that my replcement 'id' column is the
primary key and have the generated SQL create that column as the
primary key in the MySQL table.

Any help would be appreciated.

Thanks.

Hi,
demonGeek wrote:

I need to use a UUID as a primary key.

Why? It sounds to me like you're coming to the party with a solution in
mind rather than asking "how should I do this with Rails." Using a UUID as
a Primary Key implies you want it to have meaning. It shouldn't, in Rails.
Don't optimize your database structure for performance yet. Optimize your
code for maintainability. Just my $0.02.

Best regards,
Bill

Because I want to.

Like I said, I already know how to use a UUID with rails - so what is
it you think I should be asking?

The question was: how do I get a migration to create a primary key
column in a database - the only thing that has to do with Rails is
that the migration is written in Rails.

Creating a primary key on a database column is about optimizing the
database, I don't see what that has to do with optimizing Rails code.
I'm not trying to imply any meaning into that other than the obvious
one.

From
http://www.google.com/search?q=rails+create+a+primary+key+in+a+migratiion&ie
=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

http://lists.rubyonrails.org/pipermail/rails/2006-April/036134.html

HTH,
Bill

Thanks Bill, I already saw that one while researching the subject
before posting.

Unfortunately that doesn't do what I need - it just renames the
default integer 'id' key.

Since I need to change the data type of the column, the default key is
disabled (:id => false).

I had expected to be able to do something like:

      t.string :id, :limit => 36, :null => false, :primary_key => true

to explicitly inidcate the primary key column, but that doesn't seem
to be possible.

demonGeek wrote:

Thanks Bill, I already saw that one while researching the subject
before posting.

Unfortunately that doesn't do what I need - it just renames the
default integer 'id' key.

Since I need to change the data type of the column, the default key is
disabled (:id => false).

I had expected to be able to do something like:

      t.string :id, :limit => 36, :null => false, :primary_key => true

to explicitly inidcate the primary key column, but that doesn't seem
to be possible.

AFAIK you cannot use a string as the "id" in a table when using Rails.
It is part of the convention over customization mantra, which takes some
getting used to!

demonGeek wrote:

I need to use a UUID as a primary key.

I have everything working right now with the exception of being able
to create the column in MySQL (v4) as a primary key.

The migration looks like this:

create_table :groups, :id => false do |t|
      t.string :id, :limit => 36, :null => false
      t.string :name, :limit => 100, :null => false
      t.string :description, :limit => 500
      t.timestamps
end

All I want to do is specify that my replcement 'id' column is the
primary key and have the generated SQL create that column as the
primary key in the MySQL table.

Any help would be appreciated.

Thanks.

back to the question;

so rails migrations by default effectively does this,

create_table :giraffes, :id => false do
  t.column :id, :primary_key
end

http://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
line 96ish.

so, :primary_key is a column type, rather than an extra property.

This makes sense, for 99.99999% of purposes.

and, so we end up called "type_to_sql" with the type=:primary_key,
in mysql this renders "int(11) DEFAULT NULL auto_increment PRIMARY KEY"
(line 176 of the mysql connection adapter)
ie. it's not very easy to hack this...

so. my only reasonable suggestion;

create_table :giraffes, :id => false do
  t.string :id
  ...
end
execute("ALTER TABLE giraffes ADD PRIMARY KEY id")

or something, as this isn't a normal case.

My problem is very similar. I want to use id as primary key. But the
default data type of integer is too small, I would like the id field to
be able to hold a Java long (ie BIGINT in mysql, sqlserver and DB2).

There is no way that I can supply :limit to the id field? I tried to
use change_column after the table is created with id being the primary
key and that was unsuccessful (not allowed to change the column because
it is the primary key field).

Any suggestions for me?

Since my migration step will be applied to various databases (mysql,
sqlserver, db2, oracle), I don't have the option to run sql using
execute() because the sql can be different for these databases.

Any help will be greatly appreciated!

Helen Poon wrote:

My problem is very similar. I want to use id as primary key. But the
default data type of integer is too small, I would like the id field to
be able to hold a Java long (ie BIGINT in mysql, sqlserver and DB2).

There is no way that I can supply :limit to the id field? I tried to
use change_column after the table is created with id being the primary
key and that was unsuccessful (not allowed to change the column because
it is the primary key field).

Any suggestions for me?

Since my migration step will be applied to various databases (mysql,
sqlserver, db2, oracle), I don't have the option to run sql using
execute() because the sql can be different for these databases.

Any help will be greatly appreciated!

Seems the belows works:

1. create the table without primary key
2. change_column <table_name>, <the_intended_primary_key_field>,
<CHAR(10) PRIMARY KEY>

[Please quote when replying. This is a mailing list, not just a Web
forum.]

demonGeek wrote:

Because I want to.

That's not a sufficient answer. If you need a feature, be prepared to
explain how it adds value for you. If you can't explain how something
adds value, you probably don't actually need it.

Like I said, I already know how to use a UUID with rails - so what is
it you think I should be asking?

You should be thinking about *why* you want to use a UUID, and whether
it's actually the appropriate solution in the context of Rails.

The question was: how do I get a migration to create a primary key
column in a database - the only thing that has to do with Rails is
that the migration is written in Rails.

Creating a primary key on a database column is about optimizing the
database, I don't see what that has to do with optimizing Rails code.

Rails expects the database to be set up a certain way. In return, it
gives you a lot of nice stuff.

I'm not trying to imply any meaning into that other than the obvious
one.

No one said you were. But please understand the framework before trying
to fight it. :slight_smile:

I seem to recall that there's a plugin or something to make Rails work
with UUIDs, but I've never used it.

Best,

My company expects some tables to exceed 2^31-1 rows and so we are
thinking we need a 64 bit PK. Has anyone gotten this to work?

Thanks, Jeff

JeffV wrote in post #910774:

My company expects some tables to exceed 2^31-1 rows and so we are
thinking we need a 64 bit PK. Has anyone gotten this to work?

Thanks, Jeff

I have created a preliminary version of a railtie that changes
activerecord to use UUIDs, however only sqlite3 is tested and working. I
plan to add mysql support and better docs in the next few days.

Please note that it is a work in progress, but may suit your needs.

You can find it at https://github.com/standardtoaster/rails-uuid

-Andrew