Domain Constraints

What are Domain Constraints?

Domain constraints are rules that limit the values that can be stored in a column of a database table. They define the valid set of values (domain) that column values must belong to, ensuring that only appropriate data is stored in the database.

Purpose of Domain Constraints

Domain constraints serve several important purposes:

Data Validation: Ensure that only valid data enters the database
Data Consistency: Maintain uniformity in how data is stored
Error Prevention: Catch data entry errors at the database level
Business Rule Enforcement: Implement business-specific data rules

Types of Domain Constraints

1. Data Type Constraints

The most basic form of domain constraint is the data type specification. Each column in a database must have a defined data type that restricts what kind of data can be stored.

Common Data Types:

Numeric Types:
- INTEGER, SMALLINT, BIGINT (whole numbers)
- DECIMAL, NUMERIC (fixed-point numbers)
- FLOAT, REAL (floating-point numbers)
Character Types:
- CHAR(n) (fixed-length strings)
- VARCHAR(n) (variable-length strings)
- TEXT (large text data)
Date and Time Types:
- DATE (dates without time)
- TIME (time without date)
- TIMESTAMP (date and time)
Boolean Type:
- BOOLEAN (true/false values)
Binary Types:
- BINARY, VARBINARY (binary data)
- BLOB (Binary Large Objects)

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    HireDate DATE,
    Salary DECIMAL(10,2),
    IsActive BOOLEAN
);

In this example, each column can only accept values of the specified data type.

2. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have NULL values, meaning that a value must be provided for that column when inserting or updating a record.

Example:

CREATE TABLE Customers (
    CustomerID INT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

In this example, FirstName and LastName must always have values, while Email can be NULL.

3. DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during insertion.

Example:

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE DEFAULT CURRENT_DATE,
    Status VARCHAR(20) DEFAULT 'Pending',
    TotalAmount DECIMAL(10,2) DEFAULT 0.00
);

If no value is provided for OrderDate, it will default to the current date.

4. CHECK Constraint

The CHECK constraint defines a condition that values must satisfy to be accepted into a column.

Example:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2) CHECK (Price > 0),
    StockQuantity INT CHECK (StockQuantity >= 0),
    Category VARCHAR(50) CHECK (Category IN ('Electronics', 'Clothing', 'Food', 'Books'))
);

This example has three CHECK constraints:

Price must be greater than zero
StockQuantity cannot be negative
Category must be one of the specified values

5. ENUM or Domain Types (in some DBMS)

Some database systems support enumerated types or user-defined domains, which restrict a column to a predefined set of values.

Example in PostgreSQL:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name VARCHAR(100),
    current_mood mood
);

In this example, current_mood can only be ‘sad’, ‘ok’, or ‘happy’.

Advanced Domain Constraints

1. Range Constraints

Range constraints limit values to a specific range.

Example:

CREATE TABLE Students (
    StudentID INT,
    Age INT CHECK (Age BETWEEN 18 AND 100),
    GradePointAverage DECIMAL(3,2) CHECK (GradePointAverage >= 0.0 AND GradePointAverage <= 4.0)
);

2. Pattern Matching Constraints

Some DBMS allow pattern matching constraints using regular expressions.

Example in PostgreSQL:

CREATE TABLE Contacts (
    ContactID INT,
    PhoneNumber VARCHAR(20) CHECK (PhoneNumber ~ '^\d{3}-\d{3}-\d{4}$'),
    ZipCode VARCHAR(10) CHECK (ZipCode ~ '^\d{5}(-\d{4})?$')
);

This ensures phone numbers follow the pattern ‘123-456-7890’ and zip codes follow the pattern ‘12345’ or ‘12345-6789’.

3. Custom Domain Definitions (in some DBMS)

Some systems allow you to create reusable domain definitions.

Example in PostgreSQL:

CREATE DOMAIN positive_decimal AS DECIMAL(10,2) 
    CHECK (VALUE > 0);

CREATE TABLE Products (
    ProductID INT,
    Price positive_decimal,
    DiscountPrice positive_decimal
);

Implementing Domain Constraints in SQL

Defining Constraints When Creating Tables

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE CHECK (Email LIKE '%@%.%'),
    Age INT CHECK (Age >= 18),
    Salary DECIMAL(10,2) DEFAULT 0.00 CHECK (Salary >= 0),
    Department VARCHAR(50) DEFAULT 'General',
    HireDate DATE DEFAULT CURRENT_DATE
);

Adding Constraints to Existing Tables

-- Adding a NOT NULL constraint
ALTER TABLE Employees
ALTER COLUMN FirstName SET NOT NULL;

-- Adding a DEFAULT constraint
ALTER TABLE Employees
ALTER COLUMN HireDate SET DEFAULT CURRENT_DATE;

-- Adding a CHECK constraint
ALTER TABLE Employees
ADD CONSTRAINT CHK_Salary CHECK (Salary >= 0);

Removing Constraints

-- Removing a CHECK constraint
ALTER TABLE Employees
DROP CONSTRAINT CHK_Salary;

-- Removing a NOT NULL constraint
ALTER TABLE Employees
ALTER COLUMN FirstName DROP NOT NULL;

-- Removing a DEFAULT constraint
ALTER TABLE Employees
ALTER COLUMN HireDate DROP DEFAULT;

Naming Constraints

It’s a good practice to name your constraints for easier management:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100) CONSTRAINT NN_ProductName NOT NULL,
    Price DECIMAL(10,2) CONSTRAINT CHK_Price CHECK (Price > 0),
    Category VARCHAR(50) CONSTRAINT CHK_Category CHECK (Category IN ('Electronics', 'Clothing', 'Food', 'Books'))
);

Benefits of Domain Constraints

Data Integrity: Ensures stored data meets defined requirements
Error Prevention: Catches invalid data before it enters the database
Centralized Validation: Enforces rules at the database level rather than in application code
Self-Documentation: Constraints document data rules directly in the schema
Performance: Often more efficient than application-level validation