Postgresql referential integrity constraints in Rails 3

Question

Came across this post & it says referential integrity handled by the DB is not the Rails way at all.

But consider this:

class User < ActiveRecord::Base
  belongs_to :department
end

class Department < ActiveRecord::Base
  has_many :users
end

#User Model
id: integer, name: string,department_id: integer
#Department Model
id: integer, name: string

Here I need to create a new user & enforce following rules

User should be assigned to a department
the users.department_id should match departments.id
if users.department_id doesn't not match with departments.id, the record should NOT be created & raise an error.

Now, how to I accomplish this in rails way? Or Adding foreign key by dropping raw SQL in a migration is only way to go?

The "Rails way" is simpleminded, foolish, and subject to race conditions (among other things). If you care at all about your data, you will use real FKs in the database, [foreigner](https://github.com/matthuhiggins/foreigner) will help you with this until the Rails devs tone down the attitude in favor of good sense. — mu is too short, May 30 '12 at 15:43
Yes, it seems weird but hey, rails is an opinionated software :) — CuriousMind, May 31 '12 at 21:04
But their logic has gaping holes in it and they let their attitude get in the way of solid software. At least they admit the shortcomings of their approach wrt uniqueness in the [Validations Guide](http://guides.rubyonrails.org/active_record_validations_callbacks.html#why-use-validations). I've seen enough *impossible* things happen to know better and I'm at least as opinionated as Rails :) — mu is too short, May 31 '12 at 21:19
After reading the DHH quote I never would have guessed he was talking about referential integrity (is he?) but instead about stuff like the database not saving an order, because the total order value is below a minimum threshold or something like that. Anyway, @muistooshort is right of course :) — Daniel Rikowski, Oct 23 '13 at 11:21
@DanielRikowski: A foreign key *is* a constraint and is how databases enforce referential integrity; by default, Rails doesn't know what a real FK is so I think DHH was talking about referential integrity (or maybe he just doesn't understand relational databases well enough to know about FKs). Data integrity is the database's job, that's why you use one instead of a pile of flat files. I load my databases with FKs and CHECKs and triggers and all that good stuff: fixing bugs in code is easy, bugs in your database are often permanent. IMO of course but my opinion is right :) — mu is too short, Oct 23 '13 at 17:36

score 1 · Accepted Answer · answered May 30 '12 at 13:48

1

In Rails, you just add this to your User model:

validates_presence_of :department

Rails will check for a valid department before allowing a save operation. You can also use a gem like foreigner to add foreign keys to your tables as well if you desire. I usually do this because I inevitably find myself needing to bulk load data directly at some point.

answered May 30 '12 at 13:48

Lukas Eklund

6,068
1
32
33

I already had this validation but i was validating `department_id` . changing it to `department` & it works now. But I wonder why it works now & didn't work earlier? – CuriousMind May 30 '12 at 15:12
If you are validation the presence of an ID, you make sure that the ID field is filled, but not if the ID actually references an existing row. By validating the association you make sure that there is a matching row referenced by that ID. – Daniel Rikowski Oct 23 '13 at 11:27

score 1 · Answer 2 · answered May 30 '12 at 14:18

If Lukas' answer doesn't suite your needs you could also use before/after save hooks on the model

class User < ActiveRecord::Base

  before_save :validate_department

  def validate_department
     #validate the department
     #raise an error if invalid?
  end

end

Postgresql referential integrity constraints in Rails 3

2 Answers2