How Should You Decide Which Columns Get a NOT NULL Constraint?
Learn how to decide which SQL columns need a NOT NULL constraint versus which should stay nullable, with practical rules.
Expected Interview Answer
A NOT NULL constraint should be applied to any column whose absence would make a row meaningless or would break downstream logic, such as identifiers, foreign keys required by the business process, and fields that every valid record must have, while columns representing genuinely optional information should remain nullable.
The design decision comes down to distinguishing 'missing because it does not apply yet' from 'missing because something is broken.' A required field like an order's customer_id or a user's email in a system that mandates one should be NOT NULL, because a row without it is invalid by definition, and the constraint stops bad writes at the source instead of letting application code silently produce broken records. Genuinely optional attributes, like a middle name or an optional phone number, should stay nullable, since forcing a default or empty string there just replaces a clear absence with a misleading placeholder value that downstream queries may misinterpret as real data.
- Guarantees required fields are always present
- Prevents silent application bugs from writing incomplete rows
- Makes query logic simpler since required columns need no null checks
- Documents which fields are mandatory directly in the schema
AI Mentor Explanation
A team sheet submitted to the match referee is rejected outright if it is missing a captain's name, because a team cannot legally take the field without one, but the sheet is accepted even if the vice-captain field is left blank, since that role is optional. The referee's checklist enforces mandatory fields strictly while tolerating blanks in optional ones. A NOT NULL constraint enforces this exact distinction on a database row, blocking writes only where a field is genuinely mandatory.
Step-by-Step Explanation
Step 1
List every column
Enumerate the columns in the table and ask whether a row is meaningful without a value in each.
Step 2
Classify required vs optional
Mark identifiers, mandatory foreign keys, and business-critical fields as required; leave truly optional attributes nullable.
Step 3
Apply NOT NULL to required columns
Add the constraint at CREATE TABLE time or via ALTER TABLE, validating existing rows if the table already has data.
Step 4
Avoid fake defaults as a workaround
Do not force a placeholder value into an optional column just to satisfy NOT NULL; leave it nullable instead.
What Interviewer Expects
- A clear rule for distinguishing required from optional fields
- Recognition that identifiers and mandatory foreign keys are natural NOT NULL candidates
- Awareness of the risk of using placeholder defaults to dodge nullability
- Understanding that adding NOT NULL to an existing populated table requires handling current NULLs first
Common Mistakes
- Making every column NOT NULL by default without analysis
- Using empty strings or sentinel values instead of NULL for genuinely missing data
- Forgetting that existing rows must satisfy the constraint before it can be added
- Confusing NOT NULL with a default value, which are independent concerns
Best Answer (HR Friendly)
“I decide NOT NULL by asking whether a row even makes sense without that value. Fields like a primary key or a required foreign key get NOT NULL, because a row missing them is broken. Genuinely optional fields, like a middle name, stay nullable, since forcing a fake value there would just create misleading placeholder data instead of an honest absence.”
Code Example
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL, -- required: every order needs a customer
coupon_code VARCHAR(20) NULL, -- optional: not every order uses one
order_date DATE NOT NULL
);
-- Adding NOT NULL to an existing table safely
UPDATE Orders SET customer_id = 0 WHERE customer_id IS NULL; -- fix or investigate first
ALTER TABLE Orders ALTER COLUMN customer_id SET NOT NULL;Follow-up Questions
- What happens if you try to add NOT NULL to a column that already contains NULL values?
- How does NOT NULL interact with a default value on the same column?
- Should a foreign key column always be NOT NULL?
- How would you migrate a large production table to add a NOT NULL constraint with minimal downtime?
MCQ Practice
1. When is it appropriate to apply a NOT NULL constraint to a column?
NOT NULL should mark columns where a missing value would make the row invalid or break downstream logic, not be applied blindly.
2. What is a poor substitute for leaving a genuinely optional column nullable?
Forcing a fake default into an optional column to avoid NULL misleads downstream queries into treating placeholder data as real.
3. What must be true before adding NOT NULL to an existing populated column?
The database will reject or fail to enforce a NOT NULL constraint if current rows already contain NULL in that column.
Flash Cards
When should a column be NOT NULL? — When a row is invalid or meaningless without a value present in that column.
When should a column stay nullable? — When the attribute is genuinely optional and absence is a legitimate state.
Why avoid placeholder defaults for optional data? — They disguise a real absence as fake data, misleading later queries and aggregations.
What blocks adding NOT NULL to an existing column? — Any existing row already containing NULL in that column must be fixed first.