SQL Constraints

by

In this comprehensive tutorial, I am going to walk you through everything you need to know about SQL constraints. We will explore what they are, why they are non-negotiable for high-authority system architectures, and the deep technical behaviors of the six primary constraint types.

SQL Constraints

What are SQL Constraints?

In relational database management systems (RDBMS), SQL constraints are predefined rules and restrictions applied to a column or an entire table to prevent invalid data from being inserted, updated, or deleted.

Think of constraints as the digital security guards of your database architecture. If a user or an application executes an INSERT or UPDATE statement that violates a constraint rule, the database engine immediately aborts the transaction, rolls back any partial changes, and throws an error back to the client.

Why Constraints Matter for Data Integrity

When you design databases for critical infrastructure, you must maintain strict Data Integrity. This ensures that the data remains accurate, complete, and reliable over its entire lifecycle.

Constraints allow us to implement three distinct forms of integrity directly inside engines like SQL Server, PostgreSQL, MySQL, and Oracle:

  1. Entity Integrity: Ensuring that every row in a table is uniquely identifiable and not a duplicate clone of another.
  2. Referential Integrity: Guaranteeing that relationships between tables stay synchronized and that child records never point to non-existent parent records.
  3. Domain Integrity: Restricting the values inside a column to a valid, accurate range or specific data format.

Column-Level vs. Table-Level Constraints

Before we dive into the specific types of constraints, you must understand where and how they are declared. When writing raw SQL Data Definition Language (DDL), you have two distinct architectural choices for defining rules: column-level or table-level.

1. Column-Level Constraints

A column-level constraint is declared inline, right next to the data type definition of a single column. It is highly local and applies exclusively to the specific column it is attached to.

2. Table-Level Constraints

A table-level constraint is defined at the very end of the CREATE TABLE block, completely separate from individual column definitions. You are required to use table-level syntax if your business logic dictates a composite constraint—which is a rule that spans multiple columns simultaneously (such as a multi-column unique key).

Here is a quick architectural breakdown comparing the structural compatibility of both approaches:

Constraint TypeCan be Inline (Column-Level)?Can be Out-of-Line (Table-Level)?Supports Multi-Column Configurations?
NOT NULLYesNoNo
UNIQUEYesYesYes
PRIMARY KEYYesYesYes
FOREIGN KEYYesYesYes
CHECKYesYesYes
DEFAULTYesNoNo

The 6 Essential SQL Constraints Explained

Let’s dissect the six fundamental structural constraints that form the backbone of modern relational database theory.

1. The NOT NULL Constraint

By default, standard SQL columns can accept NULL values, which represent missing, unknown, or unapplied data. However, certain fields are mandatory for structural or business reasons.

The NOT NULL constraint enforces a strict rule: the column must contain a real value for every single row. If an application attempts to insert a record without providing a value for a NOT NULL field, the database will throw an immediate execution exception.

Pro Tip: Do not confuse an empty string '' or a numerical zero 0 with a NULL value. A column protected by a NOT NULL constraint will happily accept an empty string or a zero because those are technically valid data entries. It only rejects the explicit absence of data.

2. The UNIQUE Constraint

The UNIQUE constraint ensures that every single value stored within a specific column (or a combination of columns) is entirely distinct across all existing rows in that table. No duplicates allowed.

This is the ideal choice when you want to enforce uniqueness on secondary identifiers that are not the primary anchor of the table. For instance, when designing user tables for US companies, you will want to make sure that corporate identifiers like an alternate email address or an official government employee ID remain strictly unique across the entire enterprise directory.

How UNIQUE Handles NULL Values

One major technical distinction that trips up junior database developers is how UNIQUE behaves when encountering NULL values.

  • Under standard ANSI SQL rules, a UNIQUE constraint allows NULL values.
  • However, most popular database platforms (like SQL Server) only permit one single NULL value in a column protected by a UNIQUE constraint. Subsequent attempts to insert another NULL will trigger a duplicate key violation.
  • Conversely, databases like PostgreSQL allow multiple NULL values within a unique column because they treat each NULL as an unknown, distinct value.

3. The PRIMARY KEY Constraint

The PRIMARY KEY constraint is the foundational anchor of relational database design. Its purpose is singular: to uniquely identify each individual row or record within a table.

Architecturally, a PRIMARY KEY is simply a specialized structural combination of both a NOT NULL constraint and a UNIQUE constraint. When you designate a column as your primary key, the RDBMS engine automatically forces the column to reject NULL entries and guarantees that no duplicate values can ever exist.

Critical Primary Key Architectural Rules:
  • The Rule of One: A table can have one and only one primary key.
  • Composite Primary Keys: While you can only have one primary key constraint per table, that constraint can be composed of multiple columns working together as a team (defined using table-level DDL syntax).
  • Automatic Indexing: To quickly enforce uniqueness and accelerate lookups, database engines automatically generate a unique clustered index (or a unique B-tree index) on the primary key column behind the scenes.

4. The FOREIGN KEY Constraint

If the primary key is the anchor of a table, the FOREIGN KEY is the bridge that links multiple tables together. It is the primary vehicle for enforcing Referential Integrity.

A foreign key is a column (or a collection of columns) in a “child” table that points directly to a primary key or a unique key in a “parent” table. The constraint prevents invalid references by ensuring that you can never add a record to the child table containing a reference value that does not already exist inside the parent table.

Referential Integrity Actions

What happens if someone updates or deletes a critical row in the parent table that is currently referenced by dozens of rows in a child table? To handle this, SQL foreign keys allow us to define specific automated cascading actions:

  • CASCADE: If the parent row is deleted or updated, the engine automatically deletes or updates the matching child rows.
  • RESTRICT / NO ACTION: The database completely blocks the deletion or update of the parent row as long as child records are still pointing to it. This is the default protective behavior.
  • SET NULL: If the parent record disappears, the corresponding foreign key fields in the child table are instantly set to NULL (assuming the child column is not configured as NOT NULL).

5. The CHECK Constraint

The CHECK constraint allows us to implement custom business logic validation rules right at the database layer. It acts as a gatekeeper that tests an expression before allowing data modification.

Every time a row is inserted or updated, the database evaluates the condition defined inside the CHECK constraint. If the expression evaluates to TRUE or UNKNOWN (due to a NULL component), the transaction succeeds. If the expression evaluates to FALSE, the entire write operation is blocked.

Common Production Use Cases:

  • Validating that date spans make logical sense (e.g., checking that an official project completion timestamp happens after the initial start timestamp).
  • Restricting numerical bounds (e.g., ensuring a transaction amount or salary field is strictly greater than zero).
  • Restricting string parameters to specific standardized structural codes (e.g., ensuring a US shipping status column only accepts values from a set like 'PENDING', 'SHIPPED', or 'DELIVERED').

6. The DEFAULT Constraint

While not strictly a “restrictive” constraint that blocks bad data, the DEFAULT constraint is a structural rule that ensures data completeness. It provides a fallback value for a column when an explicit value is omitted during an INSERT statement.

If an application sends a query to add a new row but leaves a column completely out of the payload, the database engine steps in and populates that column with your predefined default value. If the application explicitly provides a value (even if that value is an intentional NULL), the DEFAULT rule is bypassed entirely.

Best Practices: Naming Your Constraints

If there is one piece of authoritative advice I can give you regarding database architecture, it is this: never let your database engine auto-generate names for your constraints.

When you create a constraint without an explicit name, systems like SQL Server or Oracle will assign a random, cryptic system name like SYS_C00104829 or PK__Customer__3214EC27.

When a production system throws an error or fails a migration, a generic error message stating “Violation of constraint SYS_C00104829” leaves you guessing. If you name your constraints deliberately, your error logs immediately point to the exact issue.

The Industry Standard Naming Convention

Adopt a reliable, consistent prefix pattern across your entire organization:

  • pk_ for Primary Keys (e.g., pk_accounts)
  • fk_ for Foreign Keys (e.g., fk_orders_to_customers)
  • uq_ or unq_ for Unique Keys (e.g., unq_employees_email)
  • chk_ for Check Constraints (e.g., chk_transactions_amount)
  • df_ for Default Values (e.g., df_users_created_at)

By strictly embedding these rules and constraints directly into your underlying SQL structures, you safeguard your organization’s data layer against application bugs, human error, and inconsistent data models.

You may also like the following articles: