← VCE Spreadsheets, SQL & Visualisation

Validation rules on database fields

A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 46, VCE Data Analytics - U3O1 - Data Analytics — Module 51, 7-8 Digital Technologies: Acquiring, storing and validating data — Module 25, IGCSE Computer Science 0478 — 9 Databases — Module 14, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 79, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 108, WA Computer Science ATAR Year 11 — Data Management — Module 55

Understanding Validation rules on database fields in VCE Spreadsheets, SQL & Visualisation

What it does: Validation text is the message shown when an entry breaks the validation rule. A lookup list creates a drop-down of allowed values, so other entries are rejected. A required field must have a value in it. If you leave it blank, the record is rejected. A validation rule stops invalid data being saved. Hoping people get it right does not. SkaterBoy2024 has 13 characters, which is longer than 10, so it breaks the rule. SkaterBoy has 9 characters and is fine. This validation rule checks the number range and rejects values outside it.

Common mistakes: With Or, every number passes because it is either over 10 or under 15. The rule should use And so only ages from 11 to 14 are accepted. Required: No lets the field be left empty. It should be Required: Yes so a record with no first name is rejected.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Validation rules on database fields to a class →

This module's key terms are also in a printable crossword (Modules 45–54) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A student sets the Age field in a school database to accept only numbers from 11 to 14. Someone types 25. What happens?
The database accepts 25 and fixes it to 14The database rejects the record because it breaks the validation rule
Matches
Validation Rule: >=0 And <=100 Entered mark: 150
↓
150 is outside the range 0 to 100, so the record is rejected and must be corrected to a mark within the range.
Applying
Meanings
Term
validation rule
Meaning
A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.
Practice
Field Name: Age
Testing
Gaps
Required:
Check Answer
Spots
2Data Type: Number
3Validation Rule: >=11 Or <=14

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Seal' badge — Smooth Formatter, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A student sets the Age field in a school database to accept only numbers from 11 to 14. Someone types 25. What happens?
A. The database rejects the record because it breaks the validation rule ✓
B. The database accepts 25 and fixes it to 14
A validation rule checks each entry and rejects any record that breaks the rule. It does not change the data for you.
2. Which field setting means a record cannot be saved if the field is left empty?
A. Required field ✓
B. Lookup list
A required field must have a value in it. If you leave it blank, the record is rejected.
3. Which is the best way to make sure the Year Level field only holds 7 or 8?
A. Hope everyone types the right number
B. Add a validation rule that only allows 7 or 8 ✓
A validation rule stops invalid data being saved. Hoping people get it right does not.
4. A Pet Type field uses a lookup list with Dog, Cat and Bird. What can a user do?
A. Type any animal they like
B. Pick only one of the choices from the list ✓
A lookup list gives a drop-down of allowed values, so only those choices can be entered.
5. What is the job of validation text in a database field?
A. It shows a message explaining why an entry was rejected ✓
B. It stores extra notes about the record
Validation text is the error message the user sees when an entry breaks the validation rule, for example 'Enter a mark from 0 to 100'.

Match the Pairs

Required: Yes → The field cannot be left empty, so every record must have a value in it
Validation Rule: >=0 And <=100 → Only numbers from 0 to 100 are accepted in this field
Show an error message when someone enters a bad value, to explain the rule → Validation Text: Enter a mark from 0 to 100
Limit a field so users can only pick from a lookup list of choices → Lookup list: Year 7, Year 8, Year 9
Validation Rule: >=0 And <=100 Entered mark: 150 → 150 is outside the range 0 to 100, so the record is rejected and must be corrected to a mark within the range.
Required: Yes Student name: (left blank) → A required field cannot be left blank. The record is rejected until a name is entered.

Key Term

validation rule
A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.

Fill in the Blanks

1. Field: StudentName Required: Yes
Hint: Type the setting value that makes the field compulsory, so a record cannot be saved with it empty
2. Field: Mark Validation Rule: >=0 And <=100
Hint: Type the comparison operator that means 'less than or equal to' to set the upper limit
3. Validation Rule: Between 0 And 100 Validation Text: "Enter 0 to 100"
Hint: Type the name of the property that holds the message shown when a record is rejected
4. Field: Postcode Data Type: Short Text Field Size: 4
Hint: Type the maximum number of characters allowed, based on how many digits an Australian postcode has
5. Field: YearLevel Validation Rule: In ("Year 7","Year 8")
Hint: Type the keyword that checks an entry matches one of the values in a list

Spot the Error

Question 1
A Field: Age
B Data Type: Number
C Validation Rule: >=11 Or <=14
D Validation Text: Age 11-14
Line C — With Or, every number passes because it is either over 10 or under 15. The rule should use And so only ages from 11 to 14 are accepted.
Question 2
A Field: FirstName
B Data Type: Short Text
C Required: No
D Max Length: 20
Line C — Required: No lets the field be left empty. It should be Required: Yes so a record with no first name is rejected.
Question 3
A Field: Postcode
B Data Type: Short Text
C Required: Yes
D Max Length: 40
Line D — An Australian postcode has 4 digits, so a limit of 40 characters lets bad entries through. It should be Max Length: 4.
Question 4
A Data Type: Short Text
B Validation Rule: >=0 And <=100
C Validation Text: Score 0-100
Line A — A number range rule needs a Number field. A Short Text field cannot be checked properly as a range, so the data type should be Number.
Question 5
A Field: House
B Data Type: Short Text
C Control: Drop-down List
D Limit To List: No
Line D — If Limit To List is No, a user can still type a value that is not in the list. It should be Yes so only the listed choices are accepted.

Student with a class code? Join your class →