Rails Migrations and PostgreSQL Constraints
33 comments Latest by cyy1987 Fri, 27 Aug 2010 09:29:37 GMT
A question was posed on the Rails mailing list concerning how one would go about adding CONSTRAINTs to the database tables with ActiveRecord::Migration.
One argument was raised stating that it is easier to handle these in plain SQL schema files. I disagree. :-)
Migrations to the Rescue
Databases evolve and I have recently found the Migration structure to be perfect for handling iterations and schema changes. Using the #execute method has helped move more of my code into the Ruby/Rails framework… and that just makes things easier to manage in the long-run. This is the approach that we are using at PLANET ARGON with some of our current client projects.
# db/migrate/6_add_foreign_key.rb
class AddForeignKey < ActiveRecord::Migration
def self.up
execute "ALTER TABLE bees ADD CONSTRAINT beehive_id_fkey FOREIGN KEY
(beehive_id) REFERENCES beehives (id);"
end
def self.down
execute "ALTER TABLE bees DROP CONSTRAINT beehive_id_fkey;"
end
endThis gives us an easy way to use the standard, #create_table syntax for building our tables with Ruby… and then we can slap these constraints on later.
This would add the constraints…
rake migrate VERSION=6
...run tests…
rake
...roll back…
rake migrate VERSION=5
I have found that this approach is really useful with testing in Rails. When I think that I have everything working great (without CONSTRAINTS in PostgreSQL), I run another migration to add a bunch of foreign key and data constraints to the tables and… run my tests again.
Let’s give Active Record a Hug
This has helped me gain some trust in Active Record while still giving me that comforting feeling that PostgreSQL is acting as the body guard for my data.
Even if you don’t end up using Migrations to handle these types of database schema changes, I would highly suggest that you model your implementation after this. I’ve worked with many database schemas and this just makes it easy to add your new change and run one command to commit it to the database.
...and now I go play with beehives…
Enjoying the content? Be sure to subscribe to my RSS feed.
Hopefully there’s some kind of notification so you’ll see this comment on such an old blog post :-)
I’m currently writing an application with RoR & Postgres as it’s database (I’ll let you know if it every gets anywhere close to being finished).
I’m finding the migrations side of Activerecord an absolute godsend. It would be great if some of the constraints features of Postgres were available rather than having to use the EXECUTE method.
I was wondering if the OPTIONS could be used to specify constraints, is this something you’ve looked at at all?
Big thanks for this post! :)
Unfortunately, thanks to MySQL’s idiotic habit of using inconsistent syntax, your foreign key stuff is not DB-agnostic to the “prefered” Rails DB (and also the one that many of my clients run).
The self.up will work for MySQL, but the self.down will fail, because in MySQL, foreign keys work like this:
ALTER TABLE foo ADD CONSTRAINT foo_fk …
ALTER TABLE foo DROP FOREIGN KEY foo_fk …
(sigh) If only the toy database would use:
ALTER TABLE foo DROP CONSTRAINT foo_fk
...then it would work.
Fortunately, as with the rest of Ruby, ActiveRecord can be extended to fix this. At that point, one might as well put all of the FK stuff in there as well… but that is a post for another day.
Unfortunately, thanks to MySQL’s idiotic habit of using inconsistent syntax, your foreign key stuff is not DB-agnostic to the “prefered” Rails DB (and also the one that many of my clients run).
The self.up will work for MySQL, but the self.down will fail, because in MySQL, foreign keys work like this:
ALTER TABLE foo ADD CONSTRAINT foo_fk …
ALTER TABLE foo DROP FOREIGN KEY foo_fk …
(sigh) If only the toy database would use:
ALTER TABLE foo DROP CONSTRAINT foo_fk
...then it would work.
Fortunately, as with the rest of Ruby, ActiveRecord can be extended to fix this. At that point, one might as well put all of the FK stuff in there as well… but that is a post for another day.
If migrations aren’t used to create the test or production dbs, your FKs will never be added to those databases. I recently found this: http://wiki.rubyonrails.org/rails/pages/Foreign+Key+Schema+Dumper+Plugin
Hopefully, this will fix the issue, make FKs a database agnostic part of Rails and get pushed into the main line code.
Hi, all. I’ve read this post before and up to now have also been creating / managing my PostgreSQL foreign key constraints by hand in ActiveRecord Migrations.
The developer / tinkerer in me just couldn’t resist keeping my migrations DRY (and, hopefully, DB-agnostic) – so to this end I came up with Migrations Constraints:
http://rubyforge.org/projects/mig-constraints/
Now I can specify UNIQUE, CHECK and FOREIGN KEY constraints like so:
Or, alternatively:
Hope this helps!
There’s a plugin that helps automate enforcing foreign key constraints:
http://www.redhillonrails.org/#foreign_key_associations
Plugins from RedHill rocks. Thanks.
There’s a real useful printable PDF cheatsheet for Rails Migrations here: Rails Migrations Cheatsheet
Many thinks.
ugg kids boots
http://www.uggfiles.com/ugg-boots-ultra-tall-c-54.html
Denver Broncos jerseys, Denver Broncos jerseys
56 56 7
56 56 7
YTU TY
RT
ty ty
I was wondering if the OPTIONS could be used to specify constraints, is this somethingugg boots sale you’ve looked at at all?
However, because of the high prices which these vuitton replica are sold for, an easy solution to this problem is by buying louis replica , such as louis vuitton replica are designed to look like the original.
When you look at the quality of Coach Purses , you should especially check the inside of the handbags and inside of the pockets. Fake Coach Gallery manufacturers may not be able to get adequate stitching around the corners, especially inside of the handbags. This is a great way to identify a fake. In addition to this, you should look for the Coach Hamptons logo. Check out the Coach Madison to make sure you know what it looks like.
Christian Audigier, saw a great opportunity in Ed Hardy clothing work. He approach hardy shirts in 2004 with an idea for a clothing line based around his art and tattoo work. Hardy granted Audigier a license to use his work, and hardy shirts was born. hardy shirt quickly became a sensation across the world, but nobody took to the new range more than Hollywood’s celebrity elite.
value city furniture vitamin shoppe Poptropica goo article
Really want to return to the past, back to that carefree I
This is the perfect blog for anyone who wants to know about this topic. You know so much its almost hard to argue with you (not that I really would want…HaHa). You definitely put a new spin on a subject thats been written about for years. Great stuff, just great!
ICT Learning | Mobile Phone Panel | Education | Garderning | Health | Home Improvement | Business | Business and Finance | ICT Research | Michael Jackson
This is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion. You also know how to make people rally behind it, obviously from the responses. Youve got a design here thats not too flashy, but makes a statement as big as what youre saying. Great job, indeed.
What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say. Im sure youll reach so many people with what youve got to say.
Louis Vuitton Bird Clutch M91406 Louis Vuitton Bird Clutch Louis Vuitton Bird Clutch M91405 Louis Vuitton Bird Clutch Louis Vuitton Animal Coin Purses M91389 Louis Vuitton Animal Coin Purses Louis Vuitton Flocon Coin Purse MM M58218 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58216 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58217 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58219 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58215 Louis Vuitton Flocon Coin Purse Louis Vuitton Coin Purse Chapeau M91416 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91413 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91415 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91417 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91412 Louis Vuitton Coin Purse Chapeau Louis Vuitton 4 Key Holder M91544 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91543 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91545 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91976 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M93517 Louis Vuitton 4 Key Holder Louis Vuitton Key and Change Holder M93665 Louis Vuitton Key and Change Holder Louis Vuitton Key and Change Holder M93557 Louis Vuitton Key and Change Holder Louis Vuitton Key and Change Holder M93559 Louis Vuitton Key and Change Holder Louis Vuitton Business Card Holder M91407 Louis Vuitton Business Card Holder Louis Vuitton Business Card Holder M91409 Louis Vuitton Business Card Holder Louis Vuitton Business Card Holder M91408 Louis Vuitton Business Card Holder Louis Vuitton Key And Change Holder M93638 Louis Vuitton Key And Change Holder Louis Vuitton Key And Change Holder M93639 Louis Vuitton Key And Change Holder Louis Vuitton Key and Change Holder M93640 Louis Vuitton Key And Change Holder Louis Vuitton Key and Change Holder M93560 Louis Vuitton Key And Change Holder Louis Vuitton Key And Change Holder M93637 Louis Vuitton Key And Change Holder
Louis Vuitton Elise Wallet M93529 Louis Vuitton Elise Wallet Louis Vuitton Sarah Wallet M91522 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M91523 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M91532 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M93524 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M93530 Louis Vuitton Sarah Wallet Louis Vuitton Sarah Wallet M93667 Louis Vuitton Sarah Wallet Louis Vuitton French Purse M91525 Louis Vuitton French Purse Louis Vuitton French Purse M91533 Louis Vuitton French Purse Louis Vuitton French Purse M91526 Louis Vuitton French Purse Louis Vuitton French Purse M93521 Louis Vuitton French Purse Louis Vuitton French Purse M93528 Louis Vuitton French Purse Louis Vuitton Animal Coin Purses M91386 Louis Vuitton Animal Coin Purses Louis Vuitton Animal Coin Purses M91387 Louis Vuitton Animal Coin Purses Louis Vuitton Bird Clutch M91406 Louis Vuitton Bird Clutch Louis Vuitton Bird Clutch M91405 Louis Vuitton Bird Clutch Louis Vuitton Animal Coin Purses M91389 Louis Vuitton Animal Coin Purses Louis Vuitton Flocon Coin Purse MM M58218 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58216 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58217 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58219 Louis Vuitton Flocon Coin Purse Louis Vuitton Flocon Coin Purse MM M58215 Louis Vuitton Flocon Coin Purse Louis Vuitton Coin Purse Chapeau M91416 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91413 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91415 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91417 Louis Vuitton Coin Purse Chapeau Louis Vuitton Coin Purse Chapeau M91412 Louis Vuitton Coin Purse Chapeau Louis Vuitton 4 Key Holder M91544 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91543 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91545 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M91976 Louis Vuitton 4 Key Holder Louis Vuitton 4 Key Holder M93517 Louis Vuitton 4 Key Holder Louis Vuitton Key and Change Holder M93665 Louis Vuitton Key and Change Holde
ciphone i9 i9 phone i9 3g i9 cell phone i9 touch
ave recently found the Migration structure to be perfect for handling iterations and schema changes. Using the #execute method has helped move more of my code into th
We are a professional exporter and wholesaler of brand fashion products,Moncler Double Down Coat – Blue Moncler Double Down Coat – White Moncler Eric Down Coat – Black Moncler Eric Down Coat – Black Moncler Eric Down Coat – Blue，All products have good quality,fast and safe delivery without shipping fee. Our primary goal is to meet our clients’ requirement and establish mutually pleasant business relationships with you.If you are interested, please do not hesitate to contact us.
wholesale nike shoesShop a great selection of authentic Nike shoes&Nike Air Max with reasonable price for the entire families at nike-shoes-max.com.nike shoes 100% quality guaranteed and smooth customer service.UGG Women’s Classic Cardy Boots 5819 are available with colorful knit uppers (composed of a wool blend) and a sheepskin sock liner for extra comfort.ugg boots It is detailed with three oversized wood buttons, allowing it to be styled buttoned up, australia uggslouched down, slightly unbuttoned, or completely cuffed down. They have a light and flexible EVA outsole along with a suede heel guard provides durable wear all season long. That is why it is one of several styles that have been all time favorites with women.
ds tt card
ds tti DSTT DSTT Card DSTT card for dsi DSTT Cards DSTT cards for dsi DSTTi dstti adv M3 DS real M3 DSi M3i Zero M3i Zero for DSi