Learning SQL - SQLite Tips & Tricks

Back it up or lose it!

Before you do anything to a SQLite database - do a Backup. It's just a file so you can just copy it somewhere - easy! I've had databases get corrupted after a few Alter Table commands - so YOU HAVE BEEN WARNED!

Use a script (or QiSQL) to build your database objects

If you build tables on the fly using a SQL object generator of any sort - you WILL forget all the column names, properties, keys etc. If you think you will never have to rebuild these objects or you will never need to deploy anything then you can ignore this tip.

Always add a primary key field

When you create a table always add a primary key field. I always use the same column name - ID. Then when I want to write generic applications that use the Primary Key field or pass it as a parameter - no problems.

The Primary Key field should be anonymous

Wikipedia says - "Primary keys should be anonymous integer or numeric identifiers" - and everyone knows that Wikipedia is always right! What this means is that the primary key should not be part of your data. It is only used to identify the data item in the database. Do not confuse the Primary Key with a Unique Key (more about them later). Once a Primary Key is created it should never be changed!

What is an outer join?

When you join two tables together you may sometimes want to see all the data even if the join condition has not been met. An outer join sort of creates a dummy value that will meet any join conditions you have set.

 

Please Be Patient

More soon (ish)