Create a Table That Protects Its Data · practice
Keep Asset Tags Unique
If an equipment room owns two identical cordless drills, both records may quite reasonably use the name Cordless drill. Their asset tags do a different job: each one must point to a single physical drill. Imagine scanning TOOL-104 at the lending desk. SQLite should never leave the program with two matching items to choose between.
Here is what should happen when the lending program tries to save four records:
id | asset_tag | name | What SQLite should do |
|---|---|---|---|
| 21 | TOOL-104 | Cordless drill | Save it |
| 22 | TOOL-105 | Cordless drill | Save it |
| 23 | TOOL-104 | Folding table | Refuse it |
| 24 | NULL | Projector | Refuse it |
A repeated name is harmless here, but an exact repeated asset tag makes it impossible to know which item that tag identifies.
Find the missing rule
Before you edit anything, look at the current declaration in schema.sql:
asset_tag TEXT NOT NULL,
That line already refuses the fourth record because its tag is NULL. Nothing on the line refuses the third record, though. As far as the current schema is concerned, another TOOL-104 is fine.
Open schema.sql and add UNIQUE after NOT NULL, so the line reads:
asset_tag TEXT NOT NULL UNIQUE,
Leave the rest of the table as it is, then press Run. Find the changed line inside the complete definition SQLite prints.
UNIQUE adds a rule that prevents two rows from carrying the same Cordless drill rows are still allowed because name does not have that rule.
Keep NOT NULL beside UNIQUE. The two rules solve different problems: UNIQUE refuses a repeated tag, while NOT NULL refuses a missing tag. UNIQUE by itself would still allow more than one NULL value in SQLite.
This rule catches exact repeats. With SQLite’s usual text comparison, TOOL-104 and tool-104 are different values. Making tags ignore letter case would need another rule, so we will not do that here.
Select Submit to check the rule against new records. Duplicate names with different tags should work; a repeated tag or missing tag should not. If a record behaves differently, return to the asset_tag line and make sure both NOT NULL and UNIQUE are still there.
Your table can now reject a tag that would point to two different items without banning two items that happen to share a name. That is exactly the distinction an asset tag needs to protect.
Task
Open schema.sql and make every asset_tag unique. Keep the existing NOT NULL rule on that column.
Press Run and confirm that SQLite accepts the complete table definition. Then submit your schema. Two items with different tags may have the same name, but an exact repeated tag and a NULL tag must both be refused.