This project is archived and is in readonly mode.

#300 open
Rob Anderton

Unsigned integers for MySQL

Reported by Rob Anderton | June 2nd, 2008 @ 03:20 PM

This was originally discussed but not resolved by others on the old Rails trac:

http://dev.rubyonrails.org/ticke...

I've attached a patch (with tests) that does the following:

  • changes the primary key data type for the MySQL adapter to be "int(11) unsigned"
  • changes the references (aka belongs_to) 'sexy' migration methods to generate unsigned foreign key fields by default when using MySQL (other adapters remain unchanged)
  • adds an :unsigned option (defaults to false) to table creation and modification migrations, so for example you can now do things like this:
t.integer :category_id, :null => true, :unsigned => true
change_column :suppliers, :category_id, :integer, :unsigned => false
  • updates the SchemaDumper to understand unsigned integer fields

The unsigned option will be silently ignored by all but the MySQL adaptor. I've tested with MySQL 5.1 and SQLite 3.

Comments and changes to this ticket

  • Pratik

    Pratik June 5th, 2008 @ 10:34 PM

    • State changed from “new” to “incomplete”
    • Assigned user set to “Pratik”

    Not a good idea to create unsigned primary keys by default. However, Rails should allow people to do it nevertheless. I think the format could be something like :

    create_table(:categories_suppliers, :id => :unsigned) do |t|
    ....
    end
    
  • Rob Anderton

    Rob Anderton June 9th, 2008 @ 09:15 PM

    Updated patch attached: this doesn't touch the primary keys at all, it just adds an :unsigned option to migrations.

    I have some other, non-MySQL specific, ideas for primary key options, and will submit these in a separate patch (which will also include the ability for specifying an unsigned PK in MySQL) when I've had chance to work on them.

  • Jason L Perry

    Jason L Perry September 23rd, 2008 @ 03:58 PM

    • Tag set to activerecord, migrations, patch

    @pratik: Why is it not a good idea to create unsigned primary keys by default? I've read over the mailing list thread related to this ticket (http://www.ruby-forum.com/topic/..., is your concern just regarding backward compatibility?

    That said, I'd really like to be able to use :unsigned => true in migrations, regardless of any changes to the primary keys.

  • Rob Anderton

    Rob Anderton September 30th, 2008 @ 11:12 AM

    My understanding from the discussions was that changing the default would break backwards compatibility and that even though, as I pointed out, changes to the behaviour of the :limit option on numeric columns did the same thing, it wasn't acceptable for this patch.

    I'm still intending to either release a more configurable patch (there's a bit more I'd like to do with primary keys) or a plugin. Hopefully I'll get chance over the next few weeks as it's been on my todo list for too long now :)

    In the meantime I've uploaded the monkey-patch edition, which I've been using successfully on recent projects, to my blog

  • Jérémy Lecour

    Jérémy Lecour January 7th, 2010 @ 04:51 PM

    I don't really know where Rails is right now about this, but it sure would be usefull to be able to create unsigned integer columns from migrations.

  • Gabe da Silveira

    Gabe da Silveira February 15th, 2010 @ 07:21 PM

    Going to add my request for :unsigned => true configuration option. I don't so much care about the primary key issue. But unsigned ints are extremely useful for one very common reason: IP Addresses.

  • Rob Anderton

    Rob Anderton February 15th, 2010 @ 09:54 PM

    If there was a likelihood of it making it into core I'd be happy to look at updating the patch for Rails 3. I know that other database-specific stuff has made it into core in the (almost) 2 years since I first looked at this, so maybe it'd be more acceptable now.

  • Chip

    Chip September 25th, 2010 @ 06:59 PM

    • Tag changed from activerecord, migrations, patch to mysql adapter, activerecord, migrations, patch
    • Importance changed from “” to “”

    "I know that other database-specific stuff has made it into core in the (almost) 2 years since I first looked at this, so maybe it'd be more acceptable now."

    I agree... Rails has recently added support for MySQL's 'after' functionality - showing the db_adapters are a great place for advanced DB-specific features. Rails has always done a great job incorporating best practices, and efficiently using your DB is certainly a 'best practice'.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:59 PM

    • State changed from “incomplete” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:59 PM

    • State changed from “open” to “stale”
  • machen
  • Greg Hazel

    Greg Hazel April 26th, 2011 @ 11:17 AM

    • State changed from “stale” to “open”

    [state:open]

    The primary key change is not interesting to me, but:

    +1 for unsigned integer support.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

Tickets have moved to Github

The new ticket tracker is available at https://github.com/rails/rails/issues

Shared Ticket Bins

Pages