A Date with SQLite

Now let’s use SQLiteStudio to add a table called Invoices, like this.

Note that SQLite doesn’t actually have a date type. Instead, you use SQLite’s date functions to convert back and forth to string or numeric types. If you store dates as strings in TEXT fields, SQLite uses ISO format, which makes the date easily readable in table browsers. Notice that there are no relationships. It’s just two tables.

Joins and Relations

In SQL you can join two (or more) tables without creating a relation between the tables. The purpose of the relation is not to connect the tables but rather to maintain the logical integrity of the tables. The obvious example here is that you can’t logically delete a record in the Clients table if there are any records in the Invoices table for this particular client. You can’t have invoices with no matching clients. Before SQL, making sure this didn’t happen was left to the application. SQL, on the other hand, makes sure this doesn’t happen at the database level. Your application should still check for this, but should it fail to catch it, the database itself will catch it and prevent it with an error message. This is done by declaring relations between the related tables. We will do this later.

Leave a Reply

Your email address will not be published. Required fields are marked *