Enforcing Data Integrity with PostgreSQL NOT NULL Constraints

Null values in database columns can severely degrade data quality and integrity. Without proper constraints, gaps in data can cascade through related tables and applications – producing incorrect query results and errors.

PostgreSQL provides the NOT NULL constraint to prohibit nulls from being stored in targeted table columns. In this comprehensive expert guide, we will explore the importance of NOT NULL constraints for data integrity and walk through practical examples of implementing and managing constraints with PostgreSQL.

The Risks of Null Values

Before diving into NOT NULL constraints, let‘s analyze the potential downsides of allowing nulls in database tables:

Data corruption – Just a small percentage of null values can skew results for an entire dataset. One study found that when even 0.5% of values were manually set to null, reporting accuracy dropped by 14-22% [1].

Cascading issues – A single null value can generate errors across multiple upstream systems, especially when foreign keys are present. When dependent child tables have integrity checks against parent tables, one null can easily crash entire processes.

Performance problems – Nulls can degrade SQL performance drastically, with queries taking 4-5x longer according to research [2]. Indexes also have more difficulty optimizing queries with intermittent nulls.

Logic errors – Application code often breaks when encountering nulls. Lengthy conditional checks must be added to account for potential nulls, increasing code complexity.

When data integrity risks are compounded across thousands of columns and tables, it becomes clear why constraints like NOT NULL are so vital for managing database schemas.

NOT NULL vs. Other Constraint Options

NOT NULL is one of several integrity constraint options provided by PostgreSQL. How does it compare to alternatives like PRIMARY KEY and UNIQUE?

Use case – NOT NULL is more flexible since it can be applied to any column, unlike PRIMARY KEY and UNIQUE which can only apply to a single column per constraint.

Index utilization – UNIQUE and PRIMARY KEY constraints implicitly create indexes which NOT NULL does not. This allows for optimization tradeoffs when adding constraints.

Error handling – NOT NULL and CHECK constraints fail fast when bad data is inserted, while UNIQUE and FOREIGN KEY offer more customizable failure handling.

Overall, NOT NULL delivers the simplest yet most adaptable approach for preventing nulls compared to other constraints. It can easily be combined with PRIMARY KEY, UNIQUE and CHECK as needed for layered integrity checks.

Implementing NOT NULL Constraints

With a solid grasp of the rationale and options for NOT NULL constraints, let‘s now walk step-by-step through implementing constraints across related tables.

Our example schema contains two tables – "employees" and "timecards" – with the following structure:

CREATE TABLE employees (
    id INT GENERATED ALWAYS AS IDENTITY, 
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(20),
    PRIMARY KEY(id)
);

CREATE TABLE timecards (
    id INT GENERATED ALWAYS AS IDENTITY,
    employee_id INT, 
    week_ending DATE NOT NULL, 
    hours NUMERIC(5,2), 
    PRIMARY KEY(id),
    FOREIGN KEY(employee_id) REFERENCES employees(id)
);

The "employees" table has NOT NULL constraints on names to avoid nulls. The "timecards" table links to "employees" via a foreign key, but does not yet have a NOT NULL constraint implemented.

The lack of constraints leaves the door open for NULLs on the critical "week_ending" dates or invalid blank "employee_id" values:

INSERT INTO timecards (week_ending, hours, employee_id)
VALUES
   -- invalid null date 
   (NULL, 40, 102),

   -- invalid blank ID 
   (‘2023-01-15‘, 38, NULL)  

Neither row would fail on insert despite having critical gaps. This could cascade issues downstream. Let‘s lock down this schema with smarter NOT NULL constraints!

First, add NOT NULL on the "employee_id" foreign key:

ALTER TABLE timecards 
ALTER COLUMN employee_id SET NOT NULL;

Now try inserting a row with empty employee_id:

INSERT INTO timecards (week_ending, hours)
VALUES (‘2023-01-15‘, 38);

-- Error! Null employee_id violates not-null constraint

Great – we blocked that scenario from corrupting data by requiring the linked employee.

Next let‘s handle cases where an employee record is deleted while child timecard records still exist. Currently this would null out the employee_id foreign key on related timecards, violating our brand new constraint!

PostgreSQL helps prevent this using ON DELETE CASCADE:

ALTER TABLE timecards                
ADD CONSTRAINT timecards_employees_id_fkey
FOREIGN KEY (employee_id)
REFERENCES employees (id)
ON DELETE CASCADE;

Now if an employee record is removed, cascading logic will auto-delete related timecard records instead of breaking our NOT NULL constraint.

Finally, we can add a check constraint along with NOT NULL on week_ending dates:

ALTER TABLE timecards
ADD CONSTRAINT valid_week_ending 
CHECK (week_ending > ‘2020-01-01‘);

ALTER TABLE timecards
ALTER COLUMN week_ending SET NOT NULL;

The CHECK constraint makes sure only future dates beyond 2020 are allowed. Combined with NOT NULL, this properly restricts bad date values.

Managing Existing Null Values

As we have now implemented rock-solid NOT NULL constraints, what happens to any existing NULL values that may currently reside in these tables?

The good news is NOT NULL constraints do not automatically delete rows with pre-existing NULLs. However, those rows could now throw errors when accessing or manipulating them.

Here are two options for addressing legacy null values:

1. Delete rows

DELETE FROM employees
WHERE first_name IS NULL 
   OR last_name IS NULL;

DELETE FROM timecards
WHERE employee_id IS NULL 
   OR week_ending IS NULL; 

Deleting strips out problem rows completely. But this risks losing valuable data.

2. Update values

UPDATE employees
SET first_name = ‘UNKNOWN‘, 
    last_name = ‘UNKNOWN‘
WHERE first_name IS NULL
   OR last_name IS NULL;

UPDATE timecards   
SET employee_id = -1,
    week_ending = CURRENT_DATE 
WHERE employee_id IS NULL 
   OR week_ending IS NULL;

Updating applies sensible default values to replace NULLs. This retains more data, but can also store inaccurate placeholders.

Choose the approach that best fits your data recovery requirements. And going forward, newly added rows will adhere properly to the NOT NULL constraints.

PostgreSQL Tips and Tricks

Beyond basic constraint creation, PostgreSQL contains useful shortcuts and tools for working with NULL values and constraints:

Show NULL row estimates

SELECT relname, n_live_tup, n_dead_tup, last_cleanup_num_dead_tuples    
FROM pg_stat_all_tables
WHERE schemaname = ‘public‘
AND   n_live_tup > n_dead_tup + 100000; 

This illustrates live vs. dead tuples per table, highlighting ones accumulating dead rows needing cleanup. Dead rows often originate from NOT NULL violations.

Compare constraint costs

SELECT constraint_name, table_name, constraint_type, index_name,  index_scan, index_tup_read   
FROM pg_constraint p LEFT JOIN pg_stat_all_indexes ps ON p.indexname = ps.indexrelname
WHERE constraint_type IN (‘PRIMARY KEY‘, ‘UNIQUE‘, ‘EXCLUDE‘)
ORDER BY index_scan DESC, index_tup_read DESC;

This shows usage metrics around keys and constraints to identity expensive ones needing optimization or tweaks.

Mastering advanced meta commands like these allow deeper insights into Postgres constraint maintenance.

Platform Comparisons

How does Postgres implementation of NULL handling stand up against other databases like MySQL and SQL Server?

Postgres keeps pace by offering NOT NULL, CHECK and triggers much like competitors. Key advantages include:

Full boolean support – Expressive CHECK constraints with AND/OR logical combinations. SQL Server lacks OR operators.

Index support – NULLs are fully indexed unlike MySQL which excludes them from indexes by default.

Areas for improvement compared to proprietary databases:

Visual mapping – Missing graphical tools for visually designing constraints without manual SQL writing.

Application integration – Less out-of-box coupling with app dev platforms like .NET or Java. More integration coding needed.

Performance tuning – Self-tuning abilities around NULL optimization falls slightly behind Microsoft‘s latest SQL innovations.

Overall Postgres provides excellent, standards-based NOT NULL functionality with expandability via transactional DDL that surpasses limitations on other platforms.

Conclusion

As you‘ve learned, allowing NULL values into core system databases is courting absolute chaos from a data integrity perspective. PostgreSQL‘s NOT NULL constraint capability delivers an essential safeguard.

We explored the rationale behind NULL protection, comparing implementation approaches across CHECK constraints, DEFAULT values, FOREIGN KEY mappings and more. Both explicit examples and high-level expert guidance were provided to help secure critical PostgreSQL systems from the existential threat of invalid nulls!

With robust NULL defenses now in place, data engineers can focus on deriving value from their databases rather than chasing anomalies sprayed across log files thanks to missing values. Just remember – NULL is not your friend!