Ruby on Rails deleting fixtures with foreign keys

Question

I'm having trouble setting up the tests with fixtures that use foreign keys! And I would be much appreciated if someone could help me understand this.

Let's say for example the :user_type model has a reference to the :role model, when tests get executed, and all the data in the test db is deleted to be again re-inserted, Rails deletes the data from the role model first, instead of deleting the data first from the :user_type and only then from :role.

The fixtures:

#roles.yml
technical:
  name: 'Technical'
  obs: 'User Role for technical / maintenance users!'


#user_types.yml
technic:
  role: technical
  name: 'Technic'
  is_admin: true

The tests:

#app/test/models/role_test.rb
require 'test_helper'

class RoleTest < ActiveSupport::TestCase
  fixtures :roles

  test 'save Role without name' do
    data = Role.new()
    data.valid?
    assert data.errors.added?(:name, :blank), 'Role saved without name!'
  end
end


#app/test/models/user_type_test.rb
require 'test_helper'

class UserTypeTest < ActiveSupport::TestCase
  fixtures :user_types

  test 'save User Type without role_id' do
    data = UserType.new(:name => 'public')
    data.valid?
    assert data.errors.added?(:role_id, :blank), 'User Type saved without role'
  end
end

When the tests ran for the first time, everything goes ok. Rails cleans the database (in this point is still empty, so there is no constraint violations), then the data from the fixtures gets inserted, and the tests run fine. The next time I try to run the tests they will fail because when rails starts to delete the data from the database, it starts with the role model/table instead of the user_type! Because foreign keys where defined on these models a violation will occur because user_type is still referring to data in the model table!

How should this be done properly? Is there any mechanism to tell rails the order to destroy the fixture data? I am using RubyOnRails 4 and Firebird 2.5 by the way.

I have been struggling with this for a few days and I haven’t been able to do it right!

Thank you in advance!

Answer 1

I encountered a similar problem, but I use PostgreSQL. I am posting my solution with the hope that an analogous solution exists for Firebird (which I have not personally used).

As you mentioned, when Rails executes the test suite, it starts by deleting all of the data in the database tables. This is tricky in the presence of foreign key constraints. In theory, one way to handle these constraints would be to figure out an appropriate order in which to delete records.

However, at least for PostgreSQL, Rails actually sidesteps the issue by temporarily disabling the triggers which would otherwise prevent violation of these foreign key constraints. It does this with the following (probably vendor-specific) pair of SQL commands, executed before and after the DELETE commands:

ALTER TABLE tablename DISABLE TRIGGER ALL
ALTER TABLE tablename ENABLE TRIGGER ALL

There's a catch: only a superuser role (where "role" basically means "user" in this context) can execute these commands. Effectively, Rails can't run tests unless it is using a PostgreSQL role with the superuser privilege. Maybe Firebird has superusers, too?

Side note for PostgreSQL users with this problem:

To grant superuser to a user (only do this on your test or development database)

Run this SQL statement: ALTER USER myuser WITH SUPERUSER;

This is the error message that appears as of Rails 4.2.4 when attempting to test without superuser privilege:

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: update or delete on table "tablename" violates foreign key constraint

In a future version of Rails, this more specific error message will be shown:

WARNING: Rails was not able to disable referential integrity. This is most likely caused due to missing permissions. Rails needs superuser privileges to disable referential integrity.

See rails/rails commit 72c1557 and PR #17726 for more details.

Answer 2

I had this same issue when working on a Ruby on Rails application.

I had a user model with a join table to the role and permission model:

User model

class User < ApplicationRecord
  has_many :user_permissions, dependent: :destroy
  has_many :permissions, through: :user_permissions, dependent: :destroy
  has_many :user_roles, dependent: :destroy
  has_many :roles, through: :user_roles, dependent: :destroy
end

Role model

class Role < ApplicationRecord
  has_many :role_permissions, dependent: :destroy
  has_many :permissions, through: :role_permissions, dependent: :destroy
  has_many :user_roles, dependent: :destroy
  has_many :roles, through: :user_roles, dependent: :destroy
end

Permission model

class Role < ApplicationRecord
  has_many :user_permissions, dependent: :destroy
  has_many :permissions, through: :user_permissions, dependent: :destroy
  has_many :role_permissions, dependent: :destroy
  has_many :permissions, through: :role_permissions, dependent: :destroy
end

But when I try to delete all users, roles and permissions on the application while working in the development using the command:

Permission.delete_all
Role.delete_all
User.delete_all

I get the error:

rails aborted! ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR: update or delete on table "permissions" violates foreign key constraint "fk_rails_439e640a3f" on table "role_permissions" DETAIL: Key (id)=(1) is still referenced from table "role_permissions".

Here's how I fixed it:

The issue was that the delete_all method did not call the delete operation on the associated objects since we only called it directly on the individual resources (Users, Permissions, and Roles). We also need to call it specifically on the join tables as well.

I simply modified the delete_all operation to first delete the join table association for each of the models before running the delete_all operation using the commands below:

RolePermission.delete_all
Permission.delete_all

UserRole.delete_all
Role.delete_all

User.delete_all

This time everything worked fine.