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.

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.

Using the separator for the ouput

If you wanted to use a specific separator for columns and rows while displaying the result set / table, you can use the .separator dot command which can take 2 arguments, first as the separator for the column and the second for the row.

So, if we set use .separator "|" "---" then it will split the columns with | and each row with ---.

Output mode dot command

The SQLite shell is a great way to test out and run bunch of queries, but the output might be squished or cluttered. You might need to view the output in a specific way, and the creators of SQLite were already hearing your screams.

They created not 5, not 10, but 14 different output modes, and to extend it even further, you can even use any delimiter/separator as well.