How to work with xor conditions, rails, foreign keys and sqlite database?

Question

I want this somehow with Rails 3.1:

If A set an identifier for b_id , there should not be an id for c_id . And, of course, vice versa.

I would like to do at the database level from migration (check constraint?). Is it possible? Or is it more possible to do this in a validated model?

My environment:

• Ruby 1.9.3
• Rails 3.1.3
• SQLite 3.7.3

Answer 1

You can accomplish this through polymorphic associations, since the circuit will not look exactly like yours, you can achieve the same goal by having an element A belonging to either B or C , but never for both.

You can read more here: http://guides.rubyonrails.org/association_basics.html#polymorphic-associations

In the example in this link, A is their Picture , and Employee and Proudct are your B and C :

(copied from the source linked above):

 class Picture < ActiveRecord::Base belongs_to :imageable, :polymorphic => true end class Employee < ActiveRecord::Base has_many :pictures, :as => :imageable end class Product < ActiveRecord::Base has_many :pictures, :as => :imageable end 

Answer 2

I will definitely write validations for this - it is easier to provide users with good error messages. I would also like to support this with a database constraint. It seems that control constraints can actually do the job.

Rails has no support for this, which I could find, so you need to create a table with raw sql. You will also need to change the schema builder to :sql , because the rails will not be able to create schema.rb that actually describes this.

I wrote this migration

 class CreateFoos < ActiveRecord::Migration def change execute <<SQL CREATE TABLE foos ( id INTEGER PRIMARY KEY, x_id INTEGER, y_id INTEGER, constraint xorit check( (x_id OR y_id) AND NOT(x_id AND y_id)) ) SQL end end 

Then in the rails console

 Foo.create(:x_id => 1, :y_id => 1) #=> SQLite3::ConstraintException 

How can you create a string with neither x_id nor y_id. You can change this by changing the restriction,

 (x_id IS NOT NULL OR y_id IS NOT NULL ) AND (x_id IS NULL OR y_id IS NULL) 

seemed to work for me