The SQLite database manager packs an extensive feature set into a small software library -- less than 350KB. It achieves this efficiency in part through compromises in its data types; for example, it supports integer, character and BLOB fields but not ENUM. You can work around the ENUM limitation and achieve much of the same functionality by exploiting SQLite’s other features and through careful application programming.
ENUM Data Type
When you create an enumerated, or ENUM, data type, you define a list of approved data values. For example, a data table of movie ratings includes a number of stars, from zero to four -- no movie gets a ten-star rating, no matter how good it is, and none receives a negative rating, no matter how bad it is. You can use an ENUM for the table’s star rating field, giving it the values of the integers from zero to four. Some programming languages, including Java and SQL database managers such as MySQL and Oracle include the ENUM data type, although it is not a part of the SQL 92 standard.
SQL Field Constraints
Though SQLite does not offer an ENUM data type, you can add a property called a check constraint to a field, thereby forcing it to accept values from a restricted list. For example, say you set up a state field and want to restrict its values to the set of two-character U.S. state abbreviations. When you create the table in SQLite, you set up the state field as a CHAR type and then add a check constraint that lists the 50 state abbreviations. SQLite supports check constraints as of version 3.3.0; before that version, SQLite parsed constraints but did not enforce them. If you add a constraint to a table that already has data, SQLite does not check the existing values. If a user attempts to add values to the table that are not included in the constraints, SQLite generates an error condition, blocking the transaction.
As an added test to ensure that data tables get the right values, create SQL SELECT statements that look for these values. For example, the following SELECT statement looks for movie rating records that have an incorrect number of stars:
SELECT COUNT(*) FROM movie_ratings WHERE stars NOT IN (0,1,2,3,4)
It returns a nonzero count if it finds records having star ratings lying outside the range of zero to four. Use tests such as this to verify that tables have correct values. If you find records with incorrect data, examine the check constraints for missing or extra values.
In addition to SQL statements, the logic in application programs can also check the values of data going into SQLite databases. For example, as users type information into a data entry screen form, the screen’s logic tests each value and displays a message prompting the user to fix incorrect data.
- Photo Credit Jupiterimages/Photos.com/Getty Images