Week #59

Another productive week, a lot shipped, almost all critical bugs fixed, the launch looks great. Wrote daily for another week about SQLite/SQL. Adding up 24 posts. Read it here. Generating a lot of ideas, getting back to journaling, reading instead of consuming videos and doomscrolling. It is getting better day by day. I have completed my yearly goal of reading 12 books. Still 3 more months to go, would be almost at 15-18 books.

The GENERATED column constraint

We saw certain limitations of the DEFAULT column constraint clause, that it can’t generate dynamic values based on other columns or some other dynamic popularities. For overcomming those kind of constraints, there is one more specific constraint, the GENERATED column constraint.

You can think of it as a DEFAULT but having more than static values, it is evaluated and computed each time a new row is inserted. However they come with one catch, you can’t later update the values, you need to update the values of the dependent columns first, so it will generate ALWAYS a update is made to the row(relevant columns in that row).

DEFAULT Constraint

The DEFAULT constraint in SQLite is used to specify a default value for a column, if not provided, then the column will be populated with the NULL value as the default value.

According to the docs:

The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.

Unique Constraint

The UNIQUE constraint ensures that a column in a table contains no duplicate values. Like, if you add an UNIQUE constraint to a column, then no two rows can have the exact/same value for that column.

The documentation is more nuanced.

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs. As with PRIMARY KEYs, a UNIQUE table-constraint clause must contain only column names — the use of expressions in an indexed-column of a UNIQUE table-constraint is not supported.

NOT NULL Column constraint

The NOT NULL constraint is a column-level constraint that will prevent the insertion/updation of a NULL value in that column record.

What is NULL?

In SQLite, specifically, a NULL is a value indicating that a value is not known or just not there.

NOTE: NULL is not equal to '' empty string, 0 or 0.0 (zero), it’s just cannot be compared to any value.

Some additional notes on the NOT NULL constraint:

Create Table with column constraints

We have seen the table creation with column types, we now need to look into the constraints or limitations that we can add to those columns, in order to make the data-model more robust and add some basic validations before doing any updation/mutation on the data itself.

To add a constraint, we can specify it at the end of the column name, after the type, so there are a list of constraints that could be added to column, which are relevant to specific scenarios of the actual model of the column.

Creating a table with column types

If you want to store values in a column with a specific type, you need to specify one of the following types in order to store that value in relatively type specific data.

• TEXT (string or characters, bunch of text)
• BLOB (binary data, images, raw files, etc)
• INTEGER (numbers which are whole, like 1, 67, 986, etc)
• REAL (floating or decimal point numbers, like 3.14, 85.98, 999.99, etc)
• NULL (empty or not defined)

Type affinity is what determines how a data is being stored and treated in sqlite, so we’ll need to cover that separately. For now, let’s assume that the data you give in is indeed valdiated before sending over to the database (that rarely is the case though) but for now we can avoid that discussion, and focus on creation of the structure and relations of the data.

What if you only wanted one column. If you create specify a column name in create table, then there will be actually 2 columns created, one that you specified and the other which is hidden as rowid, unless you mentioned rowid as the column.

CREATE TABLE users(name);
INSERT INTO users(name) VALUES ("abc"), ("def");

SELECT * FROM users;

You would think, you have one column name in the table users. Nope!

SELECT rowid, * from users;

+-------+------+
| rowid | name |
+-------+------+
| 1     | abc  |
| 2     | def  |
+-------+------+

Surprise! Any user defined table in sqlite almost always has a rowid column

We have explored enough sqlite dot commands, now I want to explore the actual nuts and bolts of SQL in SQLite, what better place then to create tables.

CREATE TABLE Statement

The CREATE TABLE as the name suggest will create a table in a sqlite database. What is a table?

Table is a set of relations

The syntax for the create table might look something like this:

CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [schema-name.]table-name
( column-def ( , column-def )* [, table-constraint]* )
[WITHOUT ROWID] [STRICT]

Check this diagram to make things clear for you, might even confuse you if you are absolutely new, so just stick around we will explore it one piece at a time, and as usual follow a exhaustive, all combination exploration of this statemnet.