Constraints and Data Integrity: A Guide to SQL DDL

Previously in this course, we covered the critical role of Primary Keys and Identifiers: Designing for Data Integrity and explored how Foreign Keys and Relationships: Enforcing Relational Integrity maintain the structure of your data. Today, we bridge the gap between logical design and physical implementation by focusing on column-level constraints.

While keys define the skeleton of your database, constraints are the muscle that keeps your data healthy. They ensure that the data entering your system adheres to the business rules we defined during our logical schema design.

The Role of Constraints in Data Quality

In a SaaS application, your database is the "source of truth." If your application code has a bug that allows an empty email address or a duplicate account name, that garbage data can cascade through your reports, billing systems, and user notifications.

By using SQL DDL (Data Definition Language) to define constraints, you move validation from the "application layer" (which can be bypassed or have bugs) to the "database layer" (which is immutable and strictly enforced). This is known as schema validation.

Core Constraints for SaaS Entities

We will focus on three fundamental constraints that every SaaS developer needs to master.

1. NOT NULL

This is the simplest, most effective way to prevent "missing" data. If a field is mandatory for a business process—like a user_email—you should mark it NOT NULL.

2. UNIQUE

This ensures that no two rows share the same value for a specific column. Think of account subdomains (myapp.saas.com). You cannot have two different customers using the same subdomain. A UNIQUE constraint prevents collisions at the storage level.

3. DEFAULT

Sometimes a field should have a value even if the user doesn't provide one. A DEFAULT constraint ensures that if an insertion happens without a specific value, the database fills it with a sensible baseline, such as setting a created_at timestamp or a user_status to 'active'.

Worked Example: Building the accounts Table

Let's apply these to our ongoing SaaS project. When creating an account, we need a unique identifier for the company, an email, and an account status.

SQL
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    subdomain VARCHAR(50) UNIQUE NOT NULL,
    account_status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Why this works:

• company_name cannot be empty.
• subdomain is enforced as unique; the database will reject any INSERT that attempts to duplicate an existing one.
• account_status and created_at are handled automatically by the database, reducing the surface area for errors in your application code.

Hands-on Exercise

Imagine you are building a products table for your SaaS platform. Using the syntax above, write a CREATE TABLE statement that includes:

1. A product_code that must be unique and cannot be empty.
2. A price that cannot be empty.
3. A is_active boolean column that defaults to TRUE.

Common Pitfalls

• Over-constraining: Don't apply a UNIQUE constraint to a column that might reasonably change or have duplicates in the future (like a user's phone number, which might be reused if an account is deleted).
• Ignoring NULLability: A common beginner mistake is leaving columns nullable by default. Always ask: "Can this record exist meaningfully without this piece of information?" If the answer is no, make it NOT NULL.
• Performance overhead: While constraints ensure integrity, adding too many unique indexes on a high-write table can slow down your insert performance. Use them judiciously.

FAQ

Q: Can I add a constraint after the table is already created? A: Yes, you can use the ALTER TABLE command to add constraints like UNIQUE or NOT NULL to existing columns, provided the existing data doesn't violate the new rule.

Q: What happens if I try to insert invalid data? A: The database will return an error (e.g., SQLSTATE 23505 for unique violations). Your application code must be prepared to catch these database exceptions.

Q: Are constraints just for preventing bad data? A: They are also for performance. A UNIQUE constraint creates a unique index, which makes lookups for that column significantly faster.

Recap

Constraints are the foundation of data integrity. By using NOT NULL for required fields, UNIQUE for identity enforcement, and DEFAULT for sensible baseline values, you create a robust data modeling layer that protects your system from corrupt input. Always favor database-level validation over purely application-level logic for critical business rules.

Up next: We will move from theory to action in "Setting Up the SQL Environment" by initializing your local database.