A primary key is a field whose value is different in every record, so each record can be found and never confused with another. A validation condition is a rule that a value must meet before the database accepts it. Practical ICT tasks often ask you to set both on a table you have just created.
The key protects the structure of the table. The validation rule protects the quality of the data.
They do different jobs, and a single field can have both. This page belongs to database structure and validation.
How do you choose a key?
Test each candidate field with three questions.
- Is it unique? Could two records ever hold the same value?
- Is it always present? Could the value be missing for a new record?
- Is it stable? Would the value ever need to change?
A name fails the first question. A phone number fails the third. An ID created for the table passes all three, which is why most tables use one.
How do you write a validation condition?
State what is allowed in plain words first, then convert it into a condition.
| Check | Plain rule | Condition |
|---|---|---|
| Range | Fee between 0 and 200 | >=0 AND <=200 |
| Length | ID is exactly 4 characters | LEN([MemberID])=4 |
| Presence | Name must be filled in | Required = Yes |
| Lookup | Grade is one of A, B, C | one of a list: A, B, C |
| Type | Quantity is a whole number | field type = integer |
Software varies in the exact syntax, so write the idea clearly in your answer and use the syntax your own program accepts. Boundaries matter most: ”>=0” accepts 0, but “>0” does not.
Worked example
An invented cycling club (fictional) stores members. The task: set a key and a rule for the Fee field, where a fee must be from 0 to 200 inclusive.
Step 1, choose the key. Fields are MemberID, Name, DateJoined, Fee. MemberID values are M001, M002 and so on. They are unique, always filled in and never change. MemberID is the primary key.
Step 2, state the rule. A fee may be 0, 200 or anything between. That is a range check with both ends included.
Step 3, write the condition: >=0 AND <=200
Step 4, test boundary and outside values.
| Test value | Expected | Reason |
|---|---|---|
| 0 | accepted | equal to the lower limit |
| 200 | accepted | equal to the upper limit |
| 45.50 | accepted | inside the range |
| 200.01 | rejected | above the upper limit |
| -5 | rejected | below the lower limit |
Check the table again with the condition: 0 >= 0 is true and 0 <= 200 is true, so 0 passes. 200.01 <= 200 is false, so it fails. The rule behaves as planned.
The mistake to watch for
A common slip is to believe that data passing validation must be correct.
Mistaken belief: The fee 54.50 was entered for a member whose real fee is 45.50. It passed the range check, so it must be right.
The check only tests whether the value is between 0 and 200. Both 54.50 and 45.50 are inside that range, so a swapped pair of digits goes straight through.
The correction is to describe validation as a filter for unreasonable data, not a proof of accuracy. To catch a transposed value you compare the entry with the source document, which is verification. In an exam answer, say both parts: the rule rejects impossible values, and a second check is needed for correct ones.
Check yourself
1. In a table of students with fields StudentID, FullName and Class, which field is most suitable as the primary key and why?
Show answer
StudentID. It is unique and will not change. Two students can share a name or a class.
2. Write a validation condition so that a test mark is accepted only from 0 to 100 inclusive. Name the type of check.
Show answer
>=0 AND <=100, a range check. Both 0 and 100 are accepted, and 101 or -1 are rejected.
3. A form requires an ID of exactly 4 characters. A user types M05. Which check rejects it, and why?
Show answer
A length check rejects it. M05 has 3 characters and the rule needs exactly 4.
Where this leads next
With a key and a rule in place, practise getting data into the table using importing a small fictional dataset. Earlier, choosing field types from sample values gave you the types that these rules depend on. The ICT practical task and evidence checker helps you list acceptance criteria for a task like this.
Some students can recite the check names but lose marks when asked to test a rule. A teacher in online one-to-one ICT tuition can supply new tables and push your conditions with unusual test values.