Ever since I started using Rails I've been wanting a way to enforce referential integrity in the database. The Rails philosophy is to keep business logic out of the database and do it in Ruby. All those nifty validation methods are meant to take care of that. That means no foreign key constraints in the database - just use the rails validations.
But which validations to use? The thing that's always frustrated me is that there isn't a validation to enforce that a foreign key references a record that exists. Sure, validates_presence_of will make sure you have a foreign key that isn't nil. And validates_associated will tell you if the record referenced by that key passes its own validations. But that is either too little or too much, and what I want is in the middle ground. So I decided it was time to roll my own.
Enter validates_existence_of.
class Tagging < ActiveRecord::Base
belongs_to :tag
belongs_to :taggable, :polymorphic => true
validates_existence_of :tag, :taggable
belongs_to :user
validates_existence_of :user, :allow_nil => true
end
The above example shows all the interesting bits. You can validate a simple belongs_to association, or one that is polymorphic. You can also use the :allow_nil option to allow for an optional foreign key. If the foreign key is non-nil, then the referenced record must exist.
Of course validates_existence_of only works for a belongs_to association.
Try it out for yourself:
$ script/plugin install http://svn.hasmanythrough.com/public/plugins/validates_existence/
Caveat lector: I've only tried this out on edge, but it should work fine on 1.2.3. Please let me know if you have problems with it.
If there is enough interest I'll submit this as a patch to core.
Great work, Josh! I remember running into this problem when I had first started Rails and could never figure out what the best way was to do this. Should I validatespresenceof :association, validatespresenceof :associationid, validatesassociated :association, etc. I think this plugin will definitely help newcomers and existing developers alike.
I suppose the only possible downside to validating foreign keys is that you have an extra call to the database every time you validate a record. I suppose there's no way around that besides caching, but it's something to keep in mind.
Aaron: Thanks. Yes, I think this will help simplify things a lot too.
I know there is some overhead for hitting the db to check for the exisitence. I made a patch that speeds things up a little bit (like 5%), but even without that it may not be that bad.
validates_uniqueness_ofhits the db to do a similar test, and this isn't any worse than that.Josh, one word: thanks!
This makes way too much sense.
great plugin josh. I'd suggest you submit a patch to core anyway, maybe Rick will sneak it in :)
Wonderful job Josh. I too wrestled with validatespresenceof vs validates_associated seeking a way to enforce referential integrity, but ultimately found neither satisfactory.
This is the right approach and needs to be added to the core. Thanks!
validates existence + allow nil = validates presence
validates existence - allow nil = validates presence + associated
Am I wrong? What does this plugin make easier?
Thank You
Tom: validates existence works on the field... it does not go out and check if the record referenced by that field actually exists... rails works on assumptions - which is fine 95% of the time
Tom: You have it sort of backward. validates_existence_of - allow_nil = validates_presence + require existence
validates_existence and validates_associated aren't very similar. validates_associated works on the in-memory AR object, so if it never gets written to the db, you're hosed.
validates_existence_of + allow_nil is really where the money is at. You can't do that with validates_presence_of.
and jason, you have it totally backward. validates_existence_of does check the db to see that the record exists.
Man I was just about to go try to write this the other day, but didn't quite have the time or skill. Gotta give it a test tomorrow, but I'll assume it works and say thanks, it'll be a lifesaver!
Thanks a lot, this is a really good plugin. Having it integrated to the core would be much appreciated, but maybe altering the existing validates presence should be enough ?
What's the problem with using proper foreign keys in the DB? Enforcing key constraints is not even a business logic problem, it's a data storage one.
Andrew: It's not a problem putting foreign key constraints in the DB. I know lots of people who do that, and I've done it myself on occasion. However, that doesn't integrate well with everything else you're doing in Rails. If you violate that constraint, what you get is an exception when you try to save the model object. That's ok for starters, but, you can't use the
#valid?method on a model to see if its references are, you can't change the error message, etc.I don't think you can really call it enforcing referential integrity in the database. I mean it's not on the DB level is it? I can see why it would be useful, but it's really about validation, not referential integrity.
I was intrigued to discover about six months ago that databases can enforce constraints in a way that is actually impossible from client code.
Say you have tables a and b:
CREATE TABLE a ( id INTEGER PRIMARY KEY ); CREATE TABLE b ( id INTEGER PRIMARY KEY, a_id INTEGER REFERENCES A(id) );
Now say you have two concurrent connections. The first connection says:
SQL> INSERT INTO a (id) VALUES (1); SQL> BEGIN; SQL> DELETE FROM a; (note: this transaction is uncommitted)
Now the second connection says:
SQL> INSERT INTO b (id, a_id) VALUES (1, 1);
What now? Should this command on connection two succeed or fail? The correct answer is: we don't know until connection one commits or rolls back. If you let it succeed, but connection one commits, the database will be inconsistent. It is impossible for your client code to detect this condition, because "SELECT id FROM a WHERE id=1" will continue to return that row in a, even though it has been deleted in a concurrent transaction.
A properly MVCC database will make the second connection's INSERT block until the first connection either commits or rolls back. Only then does it know whether the operation should succeed or fail.
Given this, I would really urge you to leave constraints to DBMS's. It is impossible to truly guarantee integrity otherwise.
Thanks Josh! This has been a long time coming, particularly because people get confused about the functioning of "validates_associated" and assume that it does what your plugin does. Bravo.
I hope this validation finds its way into core.
I've been thinking about this problem a lot in the last day. I thought of some ways, using row-level locks (SELECT FOR UPDATE), to get around some of the initial problems I mentioned, but then other problems would pop up.
As a Rails user and advocate, I beg you: please don't sell this as an alternative to foreign keys in a database. It cannot guarantee consistency the way that foreign keys can. It will be possible to insert data that does not follow the rules of your "validatesexistenceof" constraint. I gave one example above of how this could happen.
Joshua Haberman: I agree with what you have to say. This validation is not a replacement for referential integrity checking through foreign key constraints in the DB. If you don't need foreign key constraints, this validation can help you by providing some consistency at the model level. And if you are going to use foreign key constraints, this should be helpful by enabling you to provide a valid? test that is meaningful.
Personally, I haven't run into a situation where I've really needed foreign key constraints in the DB. Using ActiveRecord model classes (with validations) for all updates to the DB keeps me from doing something stupid to the DB. validates_existence_of gives me another tool to help me out there.
I think this is really cool, but it seems to miss the point of having referential integrity in the DB in the first place. Most companies have more than one app talking to the database. It kinda messes up the DRY idea as you're now saying every app is supposed to create a referential integrity model, when it could be consolidated in the database. This also makes tweaks to the DB's model have to cascade to all the applications' RI model.
CM: Welcome to Rails! DHH says Rails is built to work with an "application db", not an "integration db". In short, you should only have one app talking to a db. DHH views a db as a big hash table in the sky (with joins). Your concerns make sense in general, but in the world of Rails they usually don't apply.
Execellent work!!! I for one would love to see this go into core.
Offtopic slightly, I never really figured out why someone would use validates_associated? I have always thought if the associated record was inserted through its own rails model, it must have already passed validation in that model.
Good stuff Josh, I decided to use your plugin to keep my model simpler while being fully validated :) if you didn't do it yet, I would really encourage you to submit a patch.
Have you submitted this? I really think this should be part of core. I've been wanting this behavior for some time and have had my own implementation rolled up in local svn. Now that I've seen yours, I think it is certainly core-worthy.
I'm sorry, but I just can't get on board with the whole "trust rails, ditch referential integrity in the db".
My first instinct as a db admin and C# developer is to setup proper referential integrity at the db level. I live in a small town where most developers never even heard of rails (at least not in corporate) and if I walked in and mentioned an idea of dropping foreign keys they would laugh me out of town...even if they are right or wrong.
now at work, I am helping on a large C# Windows app that helps in budgeting for the entire company. some very complex joins from 4-6 tables or more. A nightmare to the "keep it simple" crowd. I read a lot of how joins are stupid, de-normalize, etc. That is fine until you need information on literally 30 topics per row.
Anyway, I am drifting in and out of sleep. Point is, the database layer of an application should be framework agnostic. Maybe that's not the whole idea of Rails and I really love Rails as I am learning it, I just don't get it.
I LIKE that extra security and it makes me sleep better at night knowing that the goobers that use my apps have one less way to f@%k things up. Sure, they will find another way but still.
Anyway, I'm going to see what I can do about my app and keeping foreign key constraints. My need are really simple at the moment so hopefully I will find a slick way to handle it.
cbmeeks http://signaldev.com
cbmeeks: If you want to do fk constraints in the db, have at it. The Rails approach often pisses off DBAs, as it essentially considers a db as a big hash table for storing objects. Eventually someone will build an object database that works with Ruby/Rails and we won't have to have these conversations anymore.
Candidate for core!
Hi John,
I was plagued by orphaned data as I had extensively used Polymorphic Associations.
it works on 1.1.6 as well and it saved my day
Works very well on 1.2.3
Exactly what I was looking for! I hope it makes it into core :)