Migrations: Manually create primary key (MySQL)

rubyonrails-talk
1

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.

2

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

3

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.

4

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

5

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.

6

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!

7

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

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.

8

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!

9

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>

10

[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,

11

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

12

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 GitHub - standardtoaster/rails-uuid: Changes ActiveRecord to use UUIDs as primary keys

-Andrew