Using SQLite

SQL Tables

The main SQL data structure is the table. Tables are used for both storage and for data manipulation. We’ve seen how to define tables with the CREATE TABLE command, but let’s look at some of the details.

A table consists of a heading and a body. The heading defines the name and type (in SQLite, the affinity) of each column. Column names must be unique within the table. The heading also defines the order of the columns, which is fixed as part of the table definition.

The table body contains all of the rows. Each row consists of one data element for each column. All of the rows in a table must have the same number of data elements, one for each column. Each element can hold exactly one data value (or a NULL).

SQL tables are allowed to hold duplicate rows. Tables can contain multiple rows where every user-defined column has an equivalent corresponding value. Duplicate rows are normally undesirable in practice, but they are allowed.

The rows in an SQL table are unordered. When a table is displayed or written down, it will have some inherent ordering, but conceptually tables have no ordering. The order of insertion has no meaning to the database.

Warning

The rows of an SQL table have no defined order.

A very common mistake is to assume a given query will always return rows in the same order. Unless you’ve specifically asked a query to sort the returned rows in a specific order, there is no guarantee the rows will continue to come back in the same order. Don’t let your application ...