DEFAULT Constraint

Question: What is a DEFAULT constraint in SQL?

Answer: A DEFAULT constraint tells the database what value to put into a column when an INSERT does not provide one. It is commonly used for things like status flags, timestamps, and simple fallback values. The key gotcha is that a default is used only when the column is omitted; if you explicitly insert NULL, the default does not automatically replace it.

Interview-Ready Answer: I use a DEFAULT constraint to give a column a fallback value when the insert statement leaves that column out. It keeps data consistent and reduces application code, especially for fields like status, priority, and created_at. The most important detail is that omitted is not the same as NULL: if I send an explicit NULL, the default does not fire. So I think of DEFAULT as the database’s backup plan, while NOT NULL and CHECK handle validation.

What it is

A DEFAULT is part of the column definition. It is a built-in fallback value that the database can supply for you, so the application does not have to repeat the same literal over and over. This is why interviewers like it: it sounds simple, but it reveals whether you understand the difference between missing and null.

How it works under the hood

1. The table schema stores the default expression in metadata, which is the database’s internal description of the table.
2. When an INSERT arrives, the engine builds a new row from the supplied values.
3. For every column you omitted, the engine checks whether a default exists.
4. If a default exists, the engine substitutes that value before constraint checks run.
5. If you explicitly supply a value, that value wins, even if it is NULL.
6. After substitution, the row is validated by rules such as NOT NULL, CHECK, UNIQUE, and foreign keys.

This means DEFAULT is not really a validator. It does not say, “this data is bad.” It says, “if you did not tell me what to use, I will choose a safe starting value.”

When to use it

• Status columns: for example, 'NEW', 'PENDING', or 'ACTIVE'.
• Audit columns: such as CURRENT_TIMESTAMP for creation time.
• Small numeric fallbacks: like a default priority of 3 or quantity of 1.
• Feature flags and booleans: for example, FALSE as the safe default.

DEFAULT vs NOT NULL vs CHECK

A useful mental model is: DEFAULT fills the blank, NOT NULL blocks emptiness, CHECK blocks bad values. They solve different problems and often work best together.

Performance and edge cases

Default lookup is basically O(1) per inserted row, so the overhead is tiny. If the default is a simple literal like 'NEW', the engine just copies it. If the default is a function such as CURRENT_TIMESTAMP, the database evaluates it at insert time; the exact timing can vary by engine, but the key point is that the value comes from the database, not the client.

• Explicit NULL: default is bypassed.
• Existing rows: adding a default later does not magically rewrite old data.
• Dialect differences: some databases can add a constant default without rewriting the whole table, while others may lock or rewrite more data. That is engine-specific.
• Bulk inserts: omitting a column is cleaner than sending the same repeated value millions of times, but the database still fills each row logically.

Memory trick: think of DEFAULT as a pre-filled sticky note on a form: if you leave the box untouched, the note stays; if you write your own answer, your choice wins. If you write NULL, that is still an explicit choice, not a blank.

Real-world story

Imagine an e-commerce checkout service with an orders table. The team sets status to default to 'PENDING' so every new order starts in the same safe state, and created_at defaults to the current time so support can trace the order lifecycle.

One day, a bulk migration script loads legacy orders and accidentally inserts NULL into status instead of omitting the column. Because DEFAULT only applies when the column is missing, those rows keep NULL. A background worker that processes WHERE status = 'PENDING' skips them, so the orders never move forward.

What goes wrong: customers see payment confirmations, but fulfillment never starts. Logs show rows with NULL status or downstream errors like “cannot process order with unknown status.” In dashboards, the number of pending orders looks strangely low, while support tickets and retries start climbing. This is exactly the kind of outage a tiny misunderstanding about DEFAULT can cause.

Follow-up & Tricky Questions:

• Can a DEFAULT use a function? Yes. Many databases allow expressions such as CURRENT_TIMESTAMP or other built-in functions, though the exact rules depend on the engine.
• Does DEFAULT run on UPDATE? Not automatically. Defaults are mainly for INSERT; some databases let you write SET col = DEFAULT in an UPDATE, but that is different from a default firing by itself.
• How do you add a default to an existing column? Use ALTER TABLE to change the column definition, but remember that old rows usually stay unchanged unless you backfill them with UPDATE.
• Can a column have both DEFAULT and NOT NULL? Absolutely, and that is a common pair. DEFAULT supplies a safe value, while NOT NULL prevents missing data from being stored.
• Is DEFAULT a good replacement for application logic? It is good for simple, stable fallback values, but not for complex business rules. Keep the rule close to the data when it is simple; keep it in the app when it depends on logic, users, or external systems.
• Tricky: If I insert NULL, does the default apply? No. NULL is an explicit value, so the default is skipped; this is the most common interview trap.
• Tricky: If I add a DEFAULT later, do old rows change? No. Existing rows usually keep their stored values, so you need a separate backfill if you want old data updated.
• Tricky: Does DEFAULT guarantee the value is valid? Not by itself. Use NOT NULL and CHECK if you need the database to reject bad data.

Common Mistakes:

• Thinking NULL triggers the default. Correction: only omitted columns use the default; explicit NULL does not.
• Assuming existing rows are backfilled automatically. Correction: changing a default affects future inserts, not old data.
• Using DEFAULT instead of NOT NULL. Correction: DEFAULT supplies a value, but NOT NULL enforces that the column cannot be empty.
• Hiding business rules in a default. Correction: keep defaults simple and predictable; use CHECK or application logic for more complex rules.

Memory Hook: Omitted gets the default; explicit wins, even if it is NULL. Think of a pre-filled form: if you leave the field alone, the pre-fill stays; if you type anything, your choice overrides it.

Cheat Sheet:

• DEFAULT fills in missing values during INSERT.
• Explicit NULL does not use the default.
• Pair DEFAULT with NOT NULL for safer data.
• Use DEFAULT for simple, repeated values like status or timestamps.
• Old rows usually do not change when you add a new default.
• Performance cost is tiny: roughly one value decision per row.

Practice Tasks:

• Create a table with a status column that defaults to 'NEW', then insert one row with the column omitted and one with an explicit value.
• Add a created_at column with CURRENT_TIMESTAMP as the default, then compare rows inserted at different times.
• Change a default on an existing table, then verify that new rows use the new value while old rows remain unchanged.