In this lesson, you will learn how to determine when a field should be mandatory and how NULL values affect your database’s integrity and query results.
When designing a schema, you must decide if a piece of information is required for an entity to exist. In database terms, this is the choice between allowing NULL or enforcing NOT NULL. A NULL value doesn’t mean "zero" or "blank"—it represents the absence of a value or "unknown" data.
When to use NOT NULL
You should mark a field as NOT NULL when the business logic dictates that a record is incomplete without it. For example, if you are tracking Orders, an order_date should almost always be mandatory. If an application attempts to insert a record without this value, the database will reject it, acting as your first line of defense for data integrity.
The Impact of NULL on Logic
When a field is nullable, you must handle its absence in your queries. Because NULL represents an unknown, it doesn't behave like a standard value in comparisons. Consider this query:
SELECT * FROM Employees
WHERE commission_rate > 0.05;
If an employee has a NULL commission_rate, they will not appear in these results. The database doesn't know if their rate is higher or lower than 0.05, so it treats the comparison as "Unknown," which evaluates to FALSE in a WHERE clause. To include them, you would need explicit logic:
SELECT * FROM Employees
WHERE commission_rate > 0.05 OR commission_rate IS NULL;
Designing for Integrity
Overusing nullable fields can lead to "hidden" bugs where your analytics or reports miss records because of unexpected NULL values. If you are uncertain whether data will be available at the time of creation, consider using a default value (like an empty string or a placeholder date) to ensure your queries remain predictable. Always default to NOT NULL unless there is a clear business reason why the information might be legitimately missing or unknown at the moment the record is created.
Key Takeaway: Use NOT NULL to enforce strict business requirements, and always account for the fact that NULL values are ignored by standard comparison operators, which can lead to silent data omission in your queries.