DB2 - Constraints

DB2 - Enforcing Data Integrity with Constraints

Introduction

Constraints are rules defined on table columns to enforce data integrity and business logic at the database level. They prevent invalid data from being entered into the database, ensuring that your data remains accurate, reliable, and consistent. Constraints are defined when a table is created or can be added later using the ALTER TABLE statement.

Let’s explore the most common types of constraints in Db2.

The NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. This is the most basic form of data validation, guaranteeing that a value must be provided for that column in every row.

Syntax and Example:

-- Create a products table where ID and name are mandatory
CREATE TABLE products (
  product_id INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  description VARCHAR(255)
);

Attempting to insert a row without a product_name will result in an error:

-- This will FAIL
INSERT INTO products (product_id, description) VALUES (101, 'A new gadget');

-- SQL0407N  Assignment of a NULL value to a NOT NULL column "...PRODUCT_NAME" is not allowed.

The UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a set of columns) are distinct. This is useful for columns that must be unique but are not the primary identifier of the table, such as an email address or a product SKU.

Syntax and Example:

CREATE TABLE users (
  user_id INT NOT NULL,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

Attempting to insert a duplicate email will fail:

INSERT INTO users VALUES (1, 'john.doe', 'john.doe@example.com');
-- This will FAIL
INSERT INTO users VALUES (2, 'jane.doe', 'john.doe@example.com');

-- SQL0803N One or more values in the INSERT statement... are not valid because the unique constraint... constrains the table from having duplicate values.

The PRIMARY KEY Constraint

A PRIMARY KEY is a special constraint that uniquely identifies each record in a table. It is implicitly a combination of NOT NULL and UNIQUE. A table can have only one primary key. It’s the most fundamental constraint for establishing relationships between tables.

Syntax and Example:

-- Create a departments table with dept_id as the primary key
CREATE TABLE departments (
  dept_id INT NOT NULL,
  dept_name VARCHAR(50) NOT NULL,
  location VARCHAR(50),
  CONSTRAINT pk_departments PRIMARY KEY (dept_id)
);

The FOREIGN KEY (Referential) Constraint

A FOREIGN KEY creates a link between two tables, enforcing referential integrity. It ensures that a value in a column (or columns) of one table (the child table) must match a value in the PRIMARY KEY of another table (the parent table). This prevents ‘orphan’ records.

Syntax and Example: Let’s create an employees table that references the departments table.

CREATE TABLE employees (
  emp_id INT NOT NULL PRIMARY KEY,
  emp_name VARCHAR(100) NOT NULL,
  department_id INT,
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id)
    REFERENCES departments (dept_id)
    ON DELETE SET NULL
);

The ON DELETE SET NULL clause specifies that if a department is deleted from the departments table, the department_id for all employees in that department will be set to NULL. Other options include ON DELETE RESTRICT (prevents deleting the department if employees exist) and ON DELETE CASCADE (deletes all employees in the department).

The CHECK Constraint

A CHECK constraint is used to enforce a specific business rule by limiting the values that are allowed in a column. The condition must evaluate to true or unknown for the value to be accepted.

Syntax and Example:

CREATE TABLE products_checked (
  product_id INT NOT NULL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  units_in_stock INT DEFAULT 0,
  CONSTRAINT chk_price CHECK (unit_price > 0),
  CONSTRAINT chk_stock CHECK (units_in_stock >= 0)
);

Any attempt to insert a product with a negative price or stock count will be rejected by the database.

Managing Constraints

You can add or remove constraints from an existing table using the ALTER TABLE command. It is best practice to always give your constraints explicit names (like pk_departments or fk_emp_dept) to make them easier to manage.

-- Add a constraint to an existing table
ALTER TABLE products ADD CONSTRAINT chk_product_name_not_empty CHECK(product_name <> '');

-- Drop a constraint from a table
ALTER TABLE products DROP CONSTRAINT chk_product_name_not_empty;

Pro Tip: Constraints are a powerful tool, but they are not a substitute for application-level validation. Use them to enforce core data integrity rules that must never be violated, and handle user-friendly error reporting and complex business logic in your application code.