Foreign keys and referential integrity


#1

Sans rails my normal approach to handling this would be to enforce it in
the database and when a database operation failed I’d try to validate
the model to produce an error message. It’s always served me pretty
well.

Rails expects you to validate before commiting but of course this does
nothing to guarantee referential integrity. It’s perfectly possible for
one process to alter the database between another processes validation
and commit. Or am I missing something?

Lets say I go with the flow anyway but try to fix this short comming by
using the database to enforce referential integrity. So I find a plugin
that adds foreign key support to migrations. All seems good so far…

Of course this means that I have to deal with loading fixtures in the
correct order but since rails loads them in the order specified by the
tests that’s no problem.

and finally the question…

Why doesn’t the test database honor the constraints? (postgres in this
case) Is there a way to disable this behavior? If my test database
correctly handled constraints what other problem would it create? I
don’t want my test environment operating differently that my production
environment?

Or maybe an even better questions…

What does everyone else do to enforce referential integrity, I’m not
interested in bucking the system or reinventing the wheel?


#2

and finally the question…

Why doesn’t the test database honor the constraints? (postgres in this
case) Is there a way to disable this behavior?

It seems this is happening because the test database structure is being
recreated from schema.rb instead of actually cloning it database with
pg_dump like it use to but I don’t entirely understand the rake tasks
that does the cloning.

Does anyone have any insight into what this is doing?

task :prepare => :environment do
Rake::Task[
{ :sql => “db:test:clone_structure”,
:ruby => db:test:clone" }
[ActiveRecord::Base.schema_format] ].invoke
end

I’m assuming that it’s optional paths to clone the database, one via
ruby migrations and the other using database tools to do the cloning?

This is the most convoluted ruby I’ve ever ran across?


#3

I can’t say that I understand the previous code snippet yet but I did
realize after digging into it bit that I could force the test database
to be cloned with straight sql by adding the following to environment.rb

ActiveRecord::Base.schema_format = :sql

Now my test database matches my development and production databases.

I haven’t tested this yet but because I’m creating the database with
migrations that work for both MySQL and Postgres and both databases
support cloning via sql everything should still work on both.

I’d still love to learn just what that rake task does if anyone
understands it?