I did the unthinkable last week. I gave up on referential integrity with my latest project. It's simply too painful to support in Ruby on Rails.
When I started the project, the first plug-in I installed was Simon Harris's Foreign Key Migrations. This plug-in rocks!
So things were cooking along and I brought on another developer. One night I got home and went through my pre-coding ritual: Get the latest code from Subversion, migrate the database, rake the tests to make sure I'm starting with a solid foundation.
Two of the tests failed.
I was livid.
How dare my colleague commit broken code to the trunk!
We started a back and forth and we eventually discovered that our databases didn't match. He was missing foreign keys that I had, and vice versa. Not good.
I shot Simon and e-mail informing him of the assumed bug and asked if he'd seen it before and had any suggestions. It was news to him. He was incredibly receptive and helpful. He asked for as much information as I could give and I sent him my entire migration set along with all the plug-ins we were using.
Simon discovered that the Engines plug-in was breaking his foreign key plug-in.
As an aside, I had worked with engines in the early days and the experience was so traumatic I swore off them for life. But my colleague had a plug-in that required them and we needed it so I caved... and shot myself in the other foot.
I thought I had fixed the problem by hacking it so that the foreign key plug-in loaded before the engines plug-in. After a fresh migration all the tests passed. But it was temporary. A few dev cycles later we found another database inconsistency.
So in the wee hours of the morning at the second or third day of Railsconf I sat outside the ballroom and wrote unit tests with fixtures for every single foreign key in the database. That's when things really started going down the tubes.
The testing framework in Rails isn't clever enough to load the fixtures in the correct order to avoid violating foreign key constraints. Ditto for the unloading between each test. It was carnage.
So I'd had it. I was fed up. I made possibly the hardest and most painful architectural decision of my software development career... I gave up on referential integrity. I removed the foreign key plug-in from trunk and committed a migration to remove all the legacy constraints. Then, I wept.
One of my biggest pet peeves is fighting the tools. Yeah it's fun to hack and extend the tools with plug-ins and monkey patches but at the end of the day if the tools are working against you instead of with you, something has to give, and in this case I think the pros of productivity, support, and community with Rails outweighed the cons of having my cake and eating it to, so I gave up. I hope I don't live to regret it.