SQL constraints for validation
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which constraint makes sure a column can never be left empty (a presence check)?
2. Which constraint stops two records from having the same value in a column, such as two students with the same email?
3. Which is the correct way to make sure age is between 0 and 120?
4. A table has the rule CHECK (age BETWEEN 0 AND 120). What happens when you try to INSERT a record with age 150?
5. Which type of validation check does CHECK (age BETWEEN 0 AND 120) perform?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1name TEXT NOT NULL | AThe words are in the wrong order. NOT NULL is the keyword pair and it should be written as name TEXT NOT NULL. | |
| 2CHECK (age BETWEEN 0 AND 120) | Bemail TEXT UNIQUE | |
| 3A constraint that stops two students being saved with the same email address | CThe condition is missing its brackets. It should be CHECK (age BETWEEN 0 AND 120). | |
| 4A constraint that only allows a mark between 0 and 100 | DA range check: the database rejects any record where age is below 0 or above 120. | |
| 5name TEXT NULL NOT | EA presence check: the database will not accept a record if the name field is left empty. | |
| 6CHECK age BETWEEN 0 AND 120 | FCHECK (mark BETWEEN 0 AND 100) |
C
Definitions
CHECK (age BETWEEN 0 AND 120)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
name TEXT ________,
2.
email TEXT ______,
3.
age INTEGER CHECK (age _______ 0 AND 120)
4.
age INTEGER _____ (age BETWEEN 0 AND 120)
5.
CREATE TABLE pupil (
age INTEGER CHECK (___ BETWEEN 0 AND 120)
);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
CREATE TABLE student (
B
id INT UNIQUE,
C
age INT CHECK age >= 0,
D
name TEXT NOT NULL
E
);
Question 2
A
CREATE TABLE pet (
B
name TEXT NO NULL,
C
id INT UNIQUE
D
);
Question 3
A
CREATE TABLE player (
B
id INT PRIMARY KEY,
C
age INT NOT NULL,
D
CHECK (age BETWEEN 0, 120)
E
);
Question 4
A
CREATE TABLE member (
B
email TEXT UNIQUE
C
age INT NOT NULL
D
);
Question 5
A
CREATE TABLE ticket (
B
id INT UNIQUE,
C
qty INT CHECK (qty < 0),
D
seat TEXT NOT NULL
E
);
Answer Key
SQL constraints for validation
Section A: Quick Questions
Q1
A — NOT NULL
Q2
A — UNIQUE
Q3
A — CHECK (age BETWEEN 0 AND 120)
Q4
B — The database rejects the record
Q5
B — A range check
Section B: Match the Pairs
Q1
name TEXT NOT NULL → A presence check: the database will not accept a record if the name field is left empty.
Q2
CHECK (age BETWEEN 0 AND 120) → A range check: the database rejects any record where age is below 0 or above 120.
Q3
A constraint that stops two students being saved with the same email address → email TEXT UNIQUE
Q4
A constraint that only allows a mark between 0 and 100 → CHECK (mark BETWEEN 0 AND 100)
Q5
name TEXT NULL NOT → The words are in the wrong order. NOT NULL is the keyword pair and it should be written as name TEXT NOT NULL.
Q6
CHECK age BETWEEN 0 AND 120 → The condition is missing its brackets. It should be CHECK (age BETWEEN 0 AND 120).
Section C: Definitions
Q1
CHECK (age BETWEEN 0 AND 120) — A constraint is a rule on a column. This CHECK rejects any record with an age outside 0 to 120. NOT NULL (must have a value) and UNIQUE (no repeats) are constraints too.Section D: Fill in the Blanks
Q1
name TEXT NOT NULL,Q2
email TEXT UNIQUE,Q3
age INTEGER CHECK (age BETWEEN 0 AND 120)Q4
age INTEGER CHECK (age BETWEEN 0 AND 120)Q5
CREATE TABLE pupil (
age INTEGER CHECK (age BETWEEN 0 AND 120)
);Section E: Spot the Error
Q1
C — The condition inside a CHECK constraint must be wrapped in brackets. It should be CHECK (age >= 0).
Q2
B — The presence check constraint is written NOT NULL, not NO NULL. It stops the column being left empty.
Q3
D — BETWEEN needs the keyword AND between the two limits, not a comma. It should be CHECK (age BETWEEN 0 AND 120).
Q4
B — There is a comma missing at the end of the line. Column definitions in CREATE TABLE are separated by commas, so it should be email TEXT UNIQUE,
Q5
C — The rule is the wrong way round. It would only accept negative quantities. To accept only positive quantities it should be CHECK (qty > 0).