A developer creates an `orders` table with a `price` column defined as VARCHAR(20). A query later computes AVG(price). What is the most likely problem?
ANo problem — VARCHAR can store numbers, and AVG converts them automatically
BThe query fails or produces incorrect results because VARCHAR columns don't support arithmetic aggregation reliably
CThe database automatically converts VARCHAR to NUMERIC for AVG whenever the values look like numbers
DThe query computes the average of the string lengths instead
Data types are not merely labels — they determine what operations are valid. Storing a price as VARCHAR means the database treats the values as text, not numbers. AVG on a text column will either fail, silently convert in inconsistent ways, or produce wrong results depending on the database. The right fix is declaring price as NUMERIC(10,2) at table creation. Choosing the correct data type is where schema design actively enforces correctness, not just storage.
Question 2 Multiple Choice
An `orders` table needs to ensure every row references a valid customer. Which constraint enforces this?
AUNIQUE constraint on the customer_id column in orders
BCHECK constraint verifying customer_id > 0
CFOREIGN KEY on customer_id referencing the primary key of the customers table
DNOT NULL constraint on customer_id in orders
FOREIGN KEY is the constraint that links one table's column to a primary key in another table, enforcing referential integrity — the database will reject an INSERT or UPDATE that references a customer_id that doesn't exist in customers. NOT NULL only prevents the value from being absent; CHECK only validates the value against a condition; UNIQUE prevents duplicates within orders but says nothing about whether the referenced customer actually exists. Referential integrity is enforced by FOREIGN KEY, not by application code.
Question 3 True / False
A PRIMARY KEY constraint on a column implies that the column is both NOT NULL and UNIQUE.
TTrue
FFalse
Answer: True
PRIMARY KEY is shorthand for NOT NULL + UNIQUE, combined into a single constraint that also signals to the database that this column is the row's identifier. Databases typically create an index on the primary key automatically. This is why declaring a PRIMARY KEY is preferred over manually adding both NOT NULL and UNIQUE — it conveys intent (this is the identifier) and enables optimizations the database can leverage.
Question 4 True / False
Adding a NOT NULL constraint to a column is sufficient to ensure that column's values are correct and meaningful.
TTrue
FFalse
Answer: False
NOT NULL only prevents a value from being absent — it says nothing about whether the value is valid. A price column with NOT NULL can still contain -999.99 or 0, which may be nonsensical for a price. To enforce domain validity, you need additional constraints: CHECK (price > 0) to require positive prices, or a FOREIGN KEY to ensure a referenced ID actually exists. Schema design involves layering constraints — NOT NULL is a starting point, not a complete solution.
Question 5 Short Answer
Why is it better to enforce data quality rules (e.g., non-negative prices, valid category values) as database constraints rather than only in application code?
Think about your answer, then reveal below.
Model answer: Database constraints are enforced by the database engine for every operation, regardless of where data enters the system. Application code can be bypassed — through direct database access, scripts, bugs, or other applications sharing the same database. Constraints act as a last line of defense at the data layer, preventing invalid data from ever entering the store.
This is the key insight behind schema design: constraints are not documentation — they are active enforcers. Once a constraint like CHECK (salary > 0) or FOREIGN KEY is in place, no path into the database can violate it without an explicit error. This prevents entire categories of bugs before application code runs. Well-designed schemas with appropriate constraints and data types make systems more reliable and reduce the cost of data quality issues downstream.