A primary key is a column or group of columns whose values uniquely identify table rows. In SQLite, declaring `id INTEGER PRIMARY KEY AUTOINCREMENT` creates unique integer identifiers automatically. section 16.3.3
A primary key shapes database operations in several practical ways:
- Rejects an insert that reuses an existing identifier, producing an `SQLError` that application code can catch with `try`. section 16.3.3
- Supports retrieval with `WHERE id = ?`, returning either the matching entry or `Nothing`. section 16.3.3
- Lets updates target one row, such as changing `checked` where `id` matches. section 16.3.3
Listing 16.8 A function to set up the database
module Todos.Database where
import Database.SQLite.Simple
setupDb :: Connection -> IO ()
setupDb dbConn =
execute_ #1
dbConn
"CREATE TABLE IF NOT EXISTS entries \ #2
\ (id INTEGER PRIMARY KEY AUTOINCREMENT, \ #2
\ text TEXT, checked INTEGER, rank INTEGER)" #2
See the complete table definition, including the auto-incrementing primary-key column. section 16.3.3
