Constraints, NULL, and Normalization

Build the mental model

A constraint is a rule the database itself enforces to protect the shape and integrity of your data.

• NOT NULL - a column must have a value
• UNIQUE - no two rows may share the same value
• FOREIGN KEY - a value must reference a real row in another table
• CHECK - a value must satisfy a condition
• PRIMARY KEY - the column (or columns) that uniquely identifies each row

These rules need to live inside the database, not only in your frontend form validation. Frontend checks can be skipped, broken by a bug, or bypassed by a second application talking to the database directly - the database is the last line of defense.

NULL means "absence," "unknown," or "not applicable," depending on context. It is not the number 0, not the boolean false, and not an empty string - those are real values, while NULL represents the lack of one.

Normalization is the practice of organizing data to avoid unnecessary duplication - storing a user's full profile once and referencing it from an orders table, instead of repeating it in every order row.

This reduces duplication, protects integrity, and makes updates safer. Denormalization - deliberately duplicating data - is a legitimate tradeoff for performance, not a mistake. PostgreSQL's "Normalization and Schema Design" lesson covers this hands-on.

NULL

The complete absence of a value - meaning "unknown," "not applicable," or "no value" depending on context, and never equivalent to 0, false, or an empty string.

Normalization

The practice of organizing tables so each fact is stored once and referenced, reducing data duplication.

DUPLICATED DATA VS NORMALIZED REFERENCE
---------------------------------------
BAD: DUPLICATED USER DATA IN EVERY ORDER ROW
-----
orders
+----+---------+------------------+-------+
| id | name    | email            | total |
+----+---------+------------------+-------+
| 1  | Aye Aye | aye@example.com  | 42.00 |
| 2  | Aye Aye | aye@example.com  | 15.50 |
| 3  | Bo Bo   | bo@example.com   |  9.00 |
+----+---------+------------------+-------+
Problem: name/email repeated every row, can drift

BETTER: NORMALIZED, ORDERS REFERENCES USERS
-----
users                    orders
+----+---------+------+  +----+---------+-------+
| id | name    | ...  |  | id | user_id | total |
+----+---------+------+  +----+---------+-------+
| 1  | Aye Aye | ...  |  | 1  | 1       | 42.00 |
| 2  | Bo Bo   | ...  |  | 2  | 1       | 15.50 |
+----+---------+------+  | 3  | 2       |  9.00 |
                          +----+---------+-------+
orders.user_id references users.id (foreign key)

Connect it to a real scenario

You declare constraints when you create or alter a table, and the database rejects any operation that would violate them.

• rejecting a duplicate email
• rejecting a missing required field
• rejecting a foreign key that points to nothing

Application code should turn these rejections into a friendly message instead of a crash. Check for NULL explicitly before comparing or displaying a value too - a null discount field is not the same as a $0 discount.

Default to normalized structures for new tables, and reach for denormalization only with a measured reason. The runnable example below shows a simple constraint validator.

• Constraints protect the database as a last line of defense even when frontend checks are skipped or bypassed
• NULL is not 0, false, or an empty string - it represents absence or unknown, depending on context, and must be handled explicitly
• Normalization reduces duplication, and denormalization is a legitimate performance tradeoff, not a mistake

Try the working example

function validateRow(row, existingRows, constraints) {
  const results = {};

  for (const field of constraints.notNull) {
    const value = row[field];
    const pass = value !== null && value !== undefined && value !== "";
    results[`NOT NULL(${field})`] = pass;
  }

  for (const field of constraints.unique) {
    const value = row[field];
    const duplicate = existingRows.some((r) => r[field] === value);
    results[`UNIQUE(${field})`] = !duplicate;
  }

  return results;
}

const existingUsers = [{ id: 1, name: "Aye", email: "aye@example.com" }];
const constraints = { notNull: ["name", "email"], unique: ["email"] };

console.log("Valid row:", validateRow(
  { name: "Bo", email: "bo@example.com" },
  existingUsers,
  constraints
));

console.log("Invalid row:", validateRow(
  { name: null, email: "aye@example.com" },
  existingUsers,
  constraints
));

Valid row: { 'NOT NULL(name)': true, 'NOT NULL(email)': true, 'UNIQUE(email)': true }
Invalid row: { 'NOT NULL(name)': false, 'NOT NULL(email)': true, 'UNIQUE(email)': false }

The invalid row fails NOT NULL(name) because name is null, and fails UNIQUE(email) because that email already exists in existingUsers.

Wikipedia: Null (SQL) — How Databases Work