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.

Week #58

What a week, almost broke prod for half an hour! On Monday, can you believe it? I didn’t but that was a bug. Patched it and moved on. Had a consistent posting and learning sessions daily for an hour about SQL and SQLite. Feels good.

Had an situation where knowing javascript was a big deal and I don’t feel like writing javascript, it just doesn’t appeal to me, I guess I have to amp that up. Anyways, one more reason added for hating javascript, its unavoidable.

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.

The .headers command is crucial as might effect the way the result set is displayed. The first row would be set to the name of the columns in the output of the relevant commands like .output, .once, or even your simple standard out queries if it is set on.

.headers on

OR

.header on

Some how either of them work. You need to set it to on to enable the headers in the output of the result set. And you can turn off with .headers off.