validator_pattern

Validator Pattern

Much of what we do in SQL is the validation of data. Virtually everything we do in SQL is the storage and manipulation of data. There are a number of methods that we use to validate the data that goes into or comes out of a table. The Validator Pattern defines a mechanism whereby only valid data can be stored in a table.

Referencing the table [utility_secure.state]; note that the combination of unique constraints and check constraints preclude the entry of data other than as follows:
- Country must be an alphabetic string of two to five lower case characters and may not be null, just what you see in the list of country abbreviations.
- State must be an alphabetic string of 0 to 128 lower case alpha-numeric characters and may not be null.
- Abbreviation must be a combination of two lower case alphabetic characters and may not be null.
- A trigger prevents data from being modified or deleted; only new values can be inserted.
- Two unique constraints ensure that the combination of country and state, and country and abbreviation are each unique.

Although there are additional data constraints on the load process (the insert statement) the "validators" are the mechanisms built into the table itself. These include the unique and check constraints, the trigger, and the data types themselves.

---

---

copyright Katherine Elizabeth Lightsey 1959-2013 (aka; my life)

"What the superior man seeks is in himself; what the small man seeks is in others." - Confucius

---

---