This might seem a little obvious but taking stock and making sanity checks as the report is built will pick up any errors early in the process. The FileTransfers table contains 22 rows and so it follows that my report, after I have created my JOINs, should also have 22 rows. The trasferID, file path and name, whether the file was sent or received, when and by whom.īefore I do this I like to take a look at the base table, in this case, the FileTransfers table to get an idea of what I expect to see in my query. We can now create a simple join to start building some sort of report, what I would like to do is have a report that shows You should see the foreign key references straight away and it shows us that the users, participants and contacts tables are all related to this table and we can see what columns are used to formulate the relationship. The content of the table looks as follows:įileTransferId integer not null primary key autoincrement,īytesTransferred integer not null, GroupId integer null,įoreign key (UserId) references Users (UserId),įoreign key (ParticipantId) references Participants (ParticipantId) on delete set null,įoreign key (ContactId) references Contacts (ContactId) on delete set null There is a table named file transfers and so this would be a logical place to start looking. In this case, as discussed above, we are interested in file transfers between users. To decide what we need to do it is, of course, useful to understand something about the investigation.
The DB I am looking at has 58 tables and working out the relations between those tables is obviously going to be long-winded, so where do we start? The rest of this blog follows the basic thought processes I might go through when initially determining which tables relate to each other and creating the query for my final report. There is a link to the SQLite website at the end of this blog where foreign keys are discussed at length. Note there is much more to foreign keys than in my explanation above, but this is sufficient to understand the main points of this blog. When they exist, they, therefore, provide a useful clue to help us understand the relationships between tables. In the example above trying to add a new message with a userID that does not exist in the users table will fail, likewise attempting to delete a user when there is a message that refers to that userID will also fail. 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 user’s table.įOREIGN KEY (userID) REFERENCES users(userID) Simply put, foreign keys provide a way of ensuring that relationships between tables are valid. This example is, of course, a little contrived but it should, I hope, show the principles well. 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. 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.
SQL is an extremely powerful programming language, and understanding SQL database schemas can often help immensely when creating queries on the database in question. Investigating a database using foreign keys
0 kommentar(er)
