SQL is an extremely powerful programming language, and understanding SQL database schemas can often help immensely when creating queries on the database in question.
The particular feature that I want to discuss in this blog is foreign keys, and I intend to show their value by example, as if I have just been asked to create a report on a database (BlackBerry Messenger master.db from an IOS device) of which I have little knowledge. To help with this let's assume I have been asked to investigate file transfers between the user of this DB and his/her contacts and create a simple report to detail the transfers.
This example is, of course, a little contrived but it should, I hope, show the principles well.
First, what are Foreign Keys?
Simply put, foreign keys provide a way of ensuring that relationships between tables are valid. For example, in the case of a simple messaging database, they can ensure that for every message with a userID there is an entry for the userID in the users table.
CREATE TABLE messages(
time INTEGER PRIMARY KEY,