# Test db missing constraints **URL:** \ **Category:** Rails\ **Created:** [February 18, 2009, 8:21pm UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688 "2009-02-18T20:21:02Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![destiney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@destiney](https://www.ruby-forum.com/u/destiney)\ **Post date:** [February 18, 2009, 8:21pm UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688/1 "2009-02-18T20:21:02Z")
What is everyone doing with regards to database constraints not being created in schema.rb ? I’m using Oracle and I just noticed none of the constraints I am creating in my migrations appear in my test schema. This makes me question the value of my test suite since I’m not running it on the same db constraints that will be present in production. I tried setting config.active\_record.schema\_format = :sql as suggested in a DHH post I found Googling, but the SQL file doesn’t contain anymore than the schema.rb contains, it’s just in a different format is all. Thanks, – Greg D. [http://destiney.com/](http://destiney.com/) ---
**Author:** ![destiney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@destiney](https://www.ruby-forum.com/u/destiney)\ **Post date:** [February 18, 2009, 9:04pm UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688/2 "2009-02-18T21:04:38Z")
Hi Greg, Here’s a simple mysql-specific example for adding cascade delete constraint, for authors----0…\*books, that should give you an idea of what to do in your oracle proj (using oracle-specific constraints): # in config/environment.rb: … config.active\_record.schema\_format = :sql … # in db/migrate/002\_create\_books.rb class CreateBooks \< ActiveRecord::Migration def self.up create\_table :books do |t| t.integer :author\_id, :null=\>false … end ``` add_index :books, :author_id ... #NOTE: mysql-specific fk-constraints. execute "alter table books add constraint fk_book_author foreign ``` key (author\_id) references authors(id) on delete cascade" end … end Then, after migrating, that constraint should show up in db/ development\_structure.sql, which your tests will then use when re- creating test db: … CREATE TABLE `books` ( … CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE … Jeff ---
**Author:** ![destiney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@destiney](https://www.ruby-forum.com/u/destiney)\ **Post date:** [February 18, 2009, 10:24pm UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688/3 "2009-02-18T22:24:02Z")
On Wed, Feb 18, 2009 at 2:03 PM, Jeff L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Then, after migrating, that constraint should show up in db/ > development\_structure.sql, which your tests will then use when re- > creating test db: > > … > CREATE TABLE `books` ( > … > CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) REFERENCES > `authors` (`id`) ON DELETE CASCADE Right, that’s what I’m saying. This does not occur for Oracle. The development\_structure.sql I get is CONSTRAINT-less. – Greg D. [http://destiney.com/](http://destiney.com/) ---
**Author:** ![destiney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@destiney](https://www.ruby-forum.com/u/destiney)\ **Post date:** [February 19, 2009, 7:01am UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688/4 "2009-02-19T07:01:11Z")
I don’t have access to oracle setup at this time, and it’s hard to tell what the problem might be without specific info about your setup … but, since you mentioned schema.rb in your op, I’m wondering if by chance you forgot to re-run your tests after mod’ing environment.rb to use sql (which results the dumping of db/ development\_structure.sql) instead of AR’s schema dumper (which results in dumping of db/schema.rb)? Assuming you did mod db/environment.rb accordingly, and you saved your oracle-specific constraints defined in the approp place(s) in your migrations files (and successfully re-migrated and confirmed that those constraints are in effect in your dev db?), try the following: $ rm db/development\_structure.sql $ rake test:units … $ cat db/development\_structure.sql … and see if those constraints don’t show up in db/ development\_structure.sql then. If they don’t, another longshot thought is … are you running an old version of rails, ie 1.x? Because iirc I used to have to do some extra fiddling to get such migrations-defined constraints to apply to test db under rails 1.x. No longer needed under rails 2.x tho. If still having problems, maybe post your specific config/setup and code? Jeff ---
**Author:** ![destiney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@destiney](https://www.ruby-forum.com/u/destiney)\ **Post date:** [February 19, 2009, 4:43pm UTC](https://www.ruby-forum.com/t/test-db-missing-constraints/160688/5 "2009-02-19T16:43:46Z")
On Thu, Feb 19, 2009 at 12:00 AM, Jeff L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > I’m wondering if by chance you forgot to re-run your tests after mod’ing > environment.rb to use sql (which results the dumping of db/ > development\_structure.sql) instead of AR’s schema dumper (which > results in dumping of db/schema.rb)? No, I did not forget. I did not even have a development\_structure.sql until I made the change in my environment.rb and re-ran my tests. > $ cat db/development\_structure.sql > … > > and see if those constraints don’t show up in db/ > development\_structure.sql then. I removed db/development\_structure.sql. rm db/development\_structure.sql I checked my config/environment.rb file: > cat config/environment.rb |grep schema\_format > config.active\_record.schema\_format = :sql I rebuilt my database: rake db:migrate VERSION=0; rake db:migrate I reran my tests. rake I checked for constraints: cat db/development\_structure.sql |grep -i constraint Nothing. > If they don’t, another longshot thought is … are you running an old > version of rails, ie 1.x? No, I am running Rails 2.2.2. cat config/environment.rb |grep 2.2.2 RAILS\_GEM\_VERSION = ‘2.2.2’ unless defined? RAILS\_GEM\_VERSION > If still having problems, maybe post your specific config/setup and > code? Sure. cat db/migrate/002\_create\_users.rb |grep -i constraint execute “alter table users add constraint pk\_users primary key (user\_id)” ruby -v ruby 1.8.7 (2008-08-11 patchlevel 72) [i486-linux] gem list --local |grep oracle activerecord-oracle\_enhanced-adapter (1.1.9) gem list --local|grep rails rails (2.2.2) What else would you like to see? – Greg D. [http://destiney.com/](http://destiney.com/)