0%

Chapter 2

Create a Table That Protects Its Data

See How SQLite Stores Each Value

The items table you met in Chapter 1 had three columns. The lending project now needs two more. An asset tag is a short code attached to one physical item, such as DRILL-01. The loan period says how many days someone may keep that item.

Here is the expanded table design beside one current row:

ColumnDeclared typeCurrent
idINTEGER1
asset_tagTEXTDRILL-01
nameTEXTCordless drill
categoryTEXTtools
loan_daysINTEGER7

Look at the values in the last column first. 1 and 7 are whole numbers. DRILL-01, Cordless drill, and tools are text. SQLite keeps track of that difference for every value it stores.

Five ways SQLite stores a value

SQLite uses five labels for stored values:

  • INTEGER for a whole number such as 7;

  • REAL for a floating-point number, such as 7.5 or 7.0;

  • TEXT for text such as Cordless drill;

  • BLOB for raw bytes, such as the contents of a small image;

  • NULL when there is no value.

The lending table currently needs only whole numbers and text, but you will meet NULL soon. REAL and BLOB complete the set, even though this table does not need them today.

Which line correctly matches all five SQLite labels to the values they describe?

SQLite calls each of these five labels a storage class. A storage class describes an actual value. The current value 7, for example, has the storage class INTEGER.

A value is not its column

The middle column of the first table describes the design, not a value that is already stored. In ordinary SQLite tables, a declared type such as INTEGER or TEXT gives a column a preference for values of that kind. SQLite calls this preference the column’s type affinity.

That leaves one useful distinction to carry forward: a column has an affinity, while each stored value has a storage class. An INTEGER affinity does not by itself promise that every value is sensible for the lending project. In the next lessons, you will write the table and add the rules that protect it.