Rails polymorphic association with multiple foreign keys and database constraints

Question

I've been playing with Multi-Table inheritance and database constraints. I know that Rails philosophy is to handle constraints in the code and not in the database but I'd like to do both. I also know about polymorphic associations but they don't let you enforce constraints.

So here is my scenario, instead of having imageable_type and imageable_id like in the guides, I'd like to have employee_id and product_id (in my case I will only have two foreign keys). That lets me create a constraint like explained here.

But now, I have a problem, I need to create a conditional relation and rails doesn't seem to provide anything for that. I would need an imageable relationship that is either linked to a product or an employee depending on which field is filled.

Any suggestion how to do that in Rails? I know how to do the constraint in SQL, my question is really how can I implement this polymorphic relation in Rails.

Once again, I know how to do that without database constraints, but database constraints are the very point of my question :)

Answer 1

Assuming you want to allow at most 1 reference to either employee or product:

CREATE TABLE picture (
  picture_id serial PRIMARY KEY
, picture text NOT NULL
, employee_id int REFERENCES employee
, product_id int REFERENCES product
, CONSTRAINT employee_xor_picture
       CHECK (employee_id IS NULL OR product_id IS NULL)
);

• employee_id and product_id each reference the PK of the respective tables and can be NULL.
• The CHECK constraint employee_xor_picture enforces that at least one of both stays NULL at all times.

It does not enforce that one of both is NOT NULL - it's typically more convenient to allow pictures that are not yet assigned, but that really depends on your requirements. To enforce that, too:

    CHECK (employee_id IS NULL AND product_id  IS NOT NULL OR
           product_id  IS NULL AND employee_id IS NOT NULL)