Thursday, November 6, 2008

Foreign Keys in SQLite

As a followup to my previous post on foreign keys in SQLite, I just wanted to point out that code to generate the triggers to emulate foreign keys has been official added to SQLite in version 3.6.4.


Brad said...

I still haven't found the need for foreign keys with SQLite. Then again, I haven't done anything complex with it. How hard is it to use these triggers?

Dave Johansen said...

I played around with them a bit and it all basically happens "under the hood" and behaves mostly like normal foreign keys.

This is probably bad, since I make such a big deal about foreign keys, but I actually don't use the triggers in the projects where I'm using SQLite. I just do the "rely on the program working" thing that you always mention and it works just fine. I guess that just reinforces your claim that the integrity part of foreign keys are only useful if you don't trust the program touching the database.

Brad said...

Even if you trust the program, I can see how foreign keys would be useful if you had multiple, different applications accessing it. Even trusted applications have bugs, and so I can see how it would be nice to enforce consistency at the database level. Still, SQLite probably isn't the right tool for the job if it comes to that :)

Dave Johansen said...

I've come to realize that when I first started playing around with SQLite that I still had so much of the "TALL mentality" ingrained in my head when it came to databases (i.e., foreign keys are only for data integrity and triggers are bad). Working with SQLite and playing around with some different ideas has really changed a lot of my views on databases (especially small client-side ones like SQLite). I now realize that the "trigger type" of aspects to foreign keys are actually their biggest strength (being able to just delete one record and have it remove all of the corresponding data is AMAZINGLY powerful) and that data integrity is vital but that sometimes the internal workings of the database isn't the most effective/efficient place for worrying about it.

I think the best way to sum what I've learned is exactly what you already alluded to ("choose the correct tool for the job at hand"). I think I was also reminded that hard/fast rules rarely work (which is basically just another way of saying the same thing, when you think about it).