This project is archived and is in readonly mode.
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:
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
- 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
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.
- 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.
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
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.
- 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'.
- 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.
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