Create a Table That Protects Its Data · practice
Reject an Impossible Loan Period
A default handles the usual loan period, but someone can still supply a number that makes no sense. Borrowing an item for zero days or for negative three days should not create a record.
Here is the behavior we want:
loan_days | What SQLite should do |
|---|---|
14 | Save 14 |
2.5 | Save 2.5 |
0 | Refuse the record |
-3 | Refuse the record |
| No value is supplied | Save the default 7 |
NULL | Refuse the record |
Require a positive number
Look again at the behavior table. Every accepted number is greater than zero, while every refused number is zero or smaller. In SQL, that comparison is written loan_days > 0.
Open schema.sql and extend the final column declaration to read:
loan_days INTEGER NOT NULL DEFAULT 7 CHECK (loan_days > 0)
Leave every other line unchanged. Press Run and find the new comparison in the complete table definition.
The loan_days > 0 compares the supplied value with zero. It is true for a positive number and false for zero or a negative number. A CHECK constraint tells SQLite to refuse a record when that comparison is false.
The other two rules on the same line still have their own jobs. DEFAULT 7 supplies the value when loan_days is omitted. NOT NULL refuses an explicitly missing value.
Notice that 2.5 is in the accepted part of the table. Although this column has INTEGER affinity, ordinary SQLite does not strictly limit it to whole numbers. A positive real number such as 2.5 can be stored here and passes the comparison.
For numeric values, this is an honest positive-number rule, not strict whole-number or general type enforcement. Enforcing whole numbers only would require another decision that we are not adding to this table.
Use Submit when the declaration loads. The table should accept positive whole and real numbers, refuse zero and negative numbers, use 7 when the value is omitted, and still refuse NULL. Once those cases behave as shown, the items table has all the protections we set out to give it.
Task
Open schema.sql and add a CHECK rule that accepts loan_days only when it is greater than zero. Keep NOT NULL and DEFAULT 7 on the same column.
Press Run, inspect the complete table definition, and submit your schema. Positive numbers, including 2.5, must be accepted. Zero and negative numbers must be refused. Omitting loan_days must still store 7, while explicitly supplying NULL must still be refused.