Migrations: Manually create primary key (MySQL)

demonGeek · May 28, 2008, 1:38am

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.

demonGeek · May 28, 2008, 2:18am

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

demonGeek · May 28, 2008, 2:39am

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.

demonGeek · May 28, 2008, 3:04am

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

----- Original Message -----
From: “demonGeek” [email protected]
To: “Ruby on Rails: Talk” [email protected]
Sent: Tuesday, May 27, 2008 8:38 PM
Subject: [Rails] Re: Migrations: Manually create primary key (MySQL)

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.

demonGeek · June 27, 2008, 12:01am

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 · May 28, 2008, 3:39am

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 · June 27, 2008, 1:09am

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

github.com

rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb

# frozen_string_literal: true

require "active_support/core_ext/string/access"
require "openssl"

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    module SchemaStatements
      include ActiveRecord::Migration::JoinTable

      # Returns a hash of mappings from the abstract data types to the native
      # database types. See TableDefinition#column for details on the recognized
      # abstract data types.
      def native_database_types
        {}
      end

      def table_options(table_name)
        nil
      end

This file has been truncated. show original

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.

demonGeek · May 5, 2010, 8:50am

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:

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

demonGeek · July 10, 2008, 9:06pm

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!

demonGeek · May 8, 2010, 7:26pm

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

demonGeek · December 25, 2010, 1:12am

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

demonGeek · May 5, 2010, 2:10pm

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

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

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]