Applying NOT NULL and UNIQUE Constraints in PostgreSQL

Previously in this course, we covered Understanding Primary Keys: Ensuring Data Integrity in PostgreSQL. While primary keys handle row identification, they don't cover every aspect of data quality. In this lesson, we add two essential tools to your toolkit: NOT NULL and UNIQUE constraints to keep your data clean at the field level.

Why Data Quality Matters

When you define a database schema, you aren't just storing values; you are defining the rules of reality for your application. If a customer table requires an email, but you don't enforce it, your code will eventually crash when it tries to send a confirmation email to a blank address. By using NOT NULL and UNIQUE, we move validation logic from the application code into the database itself, where it is impossible to bypass.

Enforcing Presence with NOT NULL

The NOT NULL constraint is the simplest way to ensure a column always contains a value. By default, PostgreSQL allows any column to be NULL (the absence of a value). Applying NOT NULL prevents a row from being inserted or updated if that column is empty.

For our store project, we need to ensure that every product has a name and a price. Here is how we apply it to our existing schema:

SQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT
);

In this example, if you attempt an INSERT that leaves the name or price blank, PostgreSQL will throw an error immediately, protecting your data integrity.

Preventing Duplicates with UNIQUE

The UNIQUE constraint ensures that no two rows in a table have the same value in a specific column (or set of columns). This is vital for business logic—for instance, you don't want two customers registered with the exact same account username.

Let’s refine our customers table to ensure email addresses are unique:

SQL
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

By adding UNIQUE to the email column, the database will reject any attempt to insert a duplicate email, preventing account collisions before they happen.

Comparing Constraints

Hands-on Exercise

Your task is to update the design of an orders table. Create a table named orders that includes:

1. id as a SERIAL PRIMARY KEY.
2. order_number which must be both UNIQUE and NOT NULL.
3. customer_id which must be NOT NULL (since an order cannot exist without a customer).

Hint: Run your CREATE TABLE statement in your psql terminal to verify it succeeds.

Common Pitfalls

• Applying constraints to existing data: If you try to add a NOT NULL constraint to a column that already contains NULL values, PostgreSQL will error out. You must clean your data first using an UPDATE to fill those gaps.
• The "NULL is not a duplicate" trap: In PostgreSQL, a UNIQUE constraint considers NULL to be a unique value. This means you could technically have multiple rows with NULL in a unique column. Always pair UNIQUE with NOT NULL if you want to strictly prevent any duplicates for a required field.
• Over-constraining: Don't make every single column NOT NULL. Some fields (like middle_name or optional_notes) should remain nullable to accommodate diverse user data.

FAQ

Q: Can I add a constraint after the table is already created? A: Yes. You can use the ALTER TABLE command (which we will cover in Managing Table Schemas).

Q: What happens if I try to insert a duplicate value? A: PostgreSQL will stop the transaction and return a unique_violation error. Your application should be prepared to catch this exception.

Q: Does UNIQUE affect performance? A: It creates an index behind the scenes, which actually improves lookup speed for that column, though it adds a tiny bit of overhead during writes.

Recap

We’ve successfully moved closer to a production-ready schema by enforcing NOT NULL to mandate data presence and UNIQUE to prevent collisions. These constraints act as the first layer of defense for your application's data quality. By defining these rules at the schema level, you ensure that even if different applications or scripts access the database, the data remains consistent and reliable.

Up next: Using CHECK Constraints to ensure values stay within logical ranges, such as ensuring prices are never negative.