Topic: Test foreign key constraints?

I have Accounts and People in my database.  An Account belongs_to a Person through the owner_id column and I have a foreign key constraint in my database between Account.owner_id and Person.id

If I run a ruby script/console then when I try to create an account that has an owner_id that is not in the database, it fails with the appropriate error:

>> account1.owner_id = 123
=> 123
>> account1.save
ActiveRecord::StatementInvalid: Mysql::Error: Cannot add or update a child row: a foreign key constraint fails

It fails because there is no Person in the database with id=123.  However when I try to do the exact same thing in my tests it succeeds.  Why is that?  Is there something I need to do to turn on foreign key constraints in the test environment?

No error produced here and the test fails (length = 1):

def test_foreign_key_constraint_on_owner
    account = Account.new(:owner_id => 123)
    account.save  # This doesn't produce an error?
    assert Account.find(:all).length == 0
end

Last edited by saracen (2007-07-11 21:45:17)

Re: Test foreign key constraints?

Does anyone know about this?  I've been trying to look for some answers but haven't found anything so far.

Re: Test foreign key constraints?

Is the schema you are using in the test database identical to the one where it works?
Are you loading any fixtures?
Can you post your model source file.
I am doing foreign key validity testing like this right now and they are working.
Rainer

Re: Test foreign key constraints?

I realized the problem here, and it's not so obvious.  The foreign key constraints aren't put into the schema.rb file, so they don't get loaded into the test database when doing a rake db:test:prepare.  The solution is to get rake to use the actual mysql schema rather than just the schema.rb file and to do that you have to uncomment this line in your environment.rb file:

  # Use SQL instead of Active Record's schema dumper when creating the test database.
  # This is necessary if your schema can't be completely dumped by the schema dumper,
  # like if you have constraints or database-specific column types
  config.active_record.schema_format = :sql