Constraints in DBMS

Consider a business where a product gets listed several times with the same product ID, or a customer's contact information is missing. What could be the result? Doubts in operations, horrible customer experience, and costly errors. A report from Precisely indicates that 67% of organizations do not completely trust their data used for decision-making. Here is when constraints in the DBMS step in to save the business.

Constraints are essential components of Database Management Systems (DBMS) that help maintain integrity and reliability over the data stored in a database. Constraints ensure the quality, consistency, and validity of the data. They define guidelines and requirements that control how data is handled, making sure it's accurate, consistent, and aligned with business needs. In the field of DBMS, constraints are essential for maintaining the accuracy and reliability of stored data.

Knowing about the types of constraints in the DBMS is important for DBAs, developers, and data professionals to build and create reliable databases with integrity over data. This article serves as a guide, exploring the different types of constraints that help enforce data integrity in DBMS.

Constraints in DBMS are rules enforced on data stored in a database that ensure accuracy, consistency, and integrity. Constraints prevent invalid entries from being made into the database, as well as define relationships between two tables. The most common types of constraints are primary key, foreign key, unique, not null, and check constraints. The purpose of these rules is to ensure valid data entry, prevent invalid transactions, and maintain the logical consistency of the database.

Constraints decide what sort of data can be in a table, and they automatically reject that data that doesn’t meet the preset rules. For example, if we mark a column in the database table as NOT NULL, the database will never accept any record with an empty value in that column.

Why are constraints important in DBMS? Constraints are important because they maintain data integrity by preventing incorrect, duplicate, or inconsistent entries. They ensure business rules by validating stored data with real-world expectations. For example, a customer’s age can’t be negative. Constraints also support relational accuracy by preserving the relationship between different tables. In addition, they automate validation and reduce manual checking by handling data rules at the database level.

Constraints at a Glance

Primary Key Constraint

The purpose of a primary key constraint is to identify each record in a table uniquely. It must contain unique and non-null values. A table can have only one primary key.          

Example: Students

 SQL

 CREATE TABLE Students (
    StudentID VARCHAR(10) PRIMARY KEY, -- Primary Key Constraint
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATE
);

Foreign Key (Referential) Constraint

The purpose of the foreign key (referential) constraint is to specify a link between two tables by referencing the primary key of another table. It implements referential integrity.         

Example: Lessons (Referenced) and Enrollments (Referencing)         

Lessons:

SQL

CREATE TABLE Lessons (
    LessonID VARCHAR(10) PRIMARY KEY,
    LessonName VARCHAR(100) NOT NULL,
    Credits INT
);

Enrollments:

SQL

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID VARCHAR(10) NOT NULL,
    LessonID VARCHAR(10) NOT NULL,
    EnrollmentDate DATE NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID), -- Foreign Key Constraint
    FOREIGN KEY (LessonID) REFERENCES Lessons(LessonID)     -- Foreign Key Constraint
); 

Unique Constraint

The purpose of a unique constraint is to ensure that all values in a specified column or set of columns are distinct from each other. A table can have multiple unique constraints, and a column with a unique constraint can contain NULL values, though typically only one NULL is allowed. Note that the primary key constraint can’t contain null values.

Example: Users

SQL

CREATE TABLE Users (
    UserID VARCHAR(10) PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL, -- Unique Constraint
    Email VARCHAR(100) UNIQUE             -- Unique Constraint (allows one NULL)
); 

Check (Table) Constraint

The purpose of a check (table) constraint is to implement a condition or rule on the data in a column or columns. It specifies a boolean expression that must be true for every row.      

Example: ProductsInventory

Here, we want StockQuantity to be non-negative (zero and above) and PricePerUnit to be positive (can't be zero)

SQL

CREATE TABLE ProductsInventory (
    ProductID VARCHAR(10) PRIMARY KEY,
    StockQuantity INT CHECK (StockQuantity >= 0), -- Check Constraint (StockQuantity)
    PricePerUnit DECIMAL(10, 2) CHECK (PricePerUnit > 0) -- Check Constraint (Price)
);

Not Null Constraint

The purpose of the Not Null constraint is to make sure that a column cannot have a NULL value. Every row must have a value for that specific column.     

Example: CustomerContacts

FirstName and LastName are mandatory.     

SQL

CREATE TABLE CustomerContacts (
    ContactID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL, -- Not Null Constraint
    LastName VARCHAR(50) NOT NULL,  -- Not Null Constraint
    PhoneNumber VARCHAR(20),
    Email VARCHAR(100)
);

Default Constraint

The purpose of the default constraint is to provide a default value for a column when no value is explicitly specified during an INSERT operation.    

Example: WebsiteOrders    
 

The Status column will automatically be 'Pending' if not provided.    

SQL

CREATE TABLE WebsiteOrders (
    OrderID VARCHAR(10) PRIMARY KEY,
    CustomerID VARCHAR(10) NOT NULL,
    OrderDate DATE NOT NULL,
    Status VARCHAR(20) DEFAULT 'Pending' -- Default Constraint
);

User-defined Constraints

The user-defined constraint refers to any constraint created by the user to implement specific business rules that may not be covered by standard PRIMARY KEY, FOREIGN KEY, UNIQUE, or NOT NULL constraints alone. These constraints are often implemented using complex CHECK constraints or database triggers.   

Example: Appointments

A user-defined constraint might be that DurationMinutes must be a multiple of 15, or that an appointment cannot last longer than 120 minutes.   

SQL

CREATE TABLE Appointments (
    AppointmentID VARCHAR(10) PRIMARY KEY,
    PatientID VARCHAR(10) NOT NULL,
    DoctorID VARCHAR(10) NOT NULL,
    AppointmentDateTime DATETIME NOT NULL,
    DurationMinutes INT,
    CONSTRAINT chk_duration_validity
        CHECK (DurationMinutes > 0 AND DurationMinutes <= 120 AND DurationMinutes % 15 = 0) -- User-defined logic via CHECK
);

Informational Constraints

Informational constraints are rules that developers or database designers know and follow, but the database itself doesn’t implement them. These rules are usually part of the business logic and are handled by the application or other processes, not directly by the database. This is often done to keep the process simple or to avoid performance issues.  

Example: StaffPerformance

An informational constraint might be: "A staff can receive an 'Excellent' rating for at most two consecutive quarters." The database won't prevent a third 'Excellent', but the application might have logic to flag or restrict this.  

SQL (No specific constraint for this informational rule in DDL)

CREATE TABLE StaffPerformance (
    StaffID INT NOT NULL,
    Quarter VARCHAR(10) NOT NULL,
    PerformanceRating VARCHAR(20) NOT NULL,
    PRIMARY KEY (StaffID, Quarter), -- Composite PK
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)
);

Domain Constraints

The purpose of Domain constraints is to define the allowed set of values for an attribute. Some databases, like PostgreSQL, let you create named custom data types (called domains) with built-in rules, like allowed values, default settings, or constraints. These are known as true domain constraints. 

In other databases, this kind of behavior isn't built-in. Instead, those databases use regular data types (like VARCHAR, INT, etc.) and add rules manually using CHECK constraints to mimic the same result. 

This ensures that values stored in a column meet a specific data type and format or fall within a specified range or set of allowed values. They are often implemented implicitly by data types and explicitly by CHECK constraints. 

Example: SensorReadings

Here, SensorType has a limited domain of text values, Value is a numeric domain, and Unit also has a limited domain. 

SQL

CREATE TABLE SensorReadings (
    ReadingID VARCHAR(10) PRIMARY KEY,
    SensorType VARCHAR(50) CHECK (SensorType IN ('Temperature', 'Humidity', 'Pressure')), -- Domain Constraint (enumerated values)
    Value DECIMAL(10, 2), -- Domain Constraint (numeric type)
    Unit VARCHAR(20) CHECK (Unit IN ('Celsius', 'Fahrenheit', 'Percent', 'hPa', 'kPa')), -- Domain Constraint
    ReadingTimestamp DATETIME NOT NULL
);

Key Constraints

Key constraints contain any attribute or set of attributes that can uniquely identify a tuple (row) in a relation. PRIMARY KEY and UNIQUE constraints, and candidate key are specific types of key constraints.

Example: LessonOfferings (Illustrating a Composite Primary Key as a Key Constraint)
 

Here, the combination of (LessonID, Semester, Year) forms a composite primary key, ensuring a Lesson is offered uniquely in a given semester and year.

SQL

CREATE TABLE LessonOfferings (
    LessonID VARCHAR(10) NOT NULL,
    Semester VARCHAR(10) NOT NULL,
    Year INT NOT NULL,
    InstructorID VARCHAR(10),
    PRIMARY KEY (LessonID, Semester, Year), -- Composite Primary Key (a type of Key Constraint)
    FOREIGN KEY (LessonID) REFERENCES Lessons(LessonID)
    -- FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID) -- Assuming an Instructors table
);

Entity Integrity Constraints

The role of Entity Integrity Constraints is to ensure that the primary key of a table has unique and non-null values for each row. So, every entity (row) in the table can be uniquely identified. The PRIMARY KEY constraint implements the Entity Integrity Constraint.    

Example: Staff 

SQL

CREATE TABLE Staff (
    StaffID INT PRIMARY KEY, -- Enforces Entity Integrity
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

Referential Integrity Constraints

The role of the Referential Integrity Constraint is to ensure that the relationships between tables are maintained. If a foreign key value exists in a referencing table, it must have a matching primary key value in the referenced table, or it must be NULL. This is to prevent “orphan” records. FOREIGN KEY constraints implement Referential Integrity Constraints.  

Example: Divisions (Referenced Table) and Staff (Referencing Table)  

Divisions Table: 

SQL

CREATE TABLE Divisions (
    DivisionID INT PRIMARY KEY,
    DivisionName VARCHAR(100) NOT NULL UNIQUE
);

Staff Table (with Foreign Key):

SQL

CREATE TABLE Staff (
    StaffID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DivisionID INT,
    FOREIGN KEY (DivisionID) REFERENCES Divisions(DivisionID) -- Enforces Referential Integrity
);

Tuple Uniqueness Constraints

The role of the Tuple Uniqueness Constraint is to guarantee that each row (tuple) in a table is unique. The column values of two rows can’t be identical. PRIMARY KEY and UNIQUE constraints implement Tuple Uniqueness Constraints.

Example: Products

Both ProductID and SKU contribute to tuple uniqueness.

SQL

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,           -- Ensures tuple uniqueness via Primary Key
    ProductName VARCHAR(100) NOT NULL,
    SKU VARCHAR(50) UNIQUE               -- Ensures tuple uniqueness for SKU
);

1. E-commerce Orders

Consider an online retailer that implements database constraints to ensure that each order has valid product IDs and the ordered quantity does not exceed available stock. Foreign key constraints link orders to customers and products, while check constraints enforce positive quantities to prevent anomalies such as orders for unavailable products or negative quantities. 

2. Bank Transactions

Check constraints in a banking system ensure that bank account balances never fall below zero, while foreign key constraints ensure that each transaction references a valid account. Unique constraints prevent duplicate transaction IDs, and triggers enforce business rules such as daily transfer limits. 

3. Healthcare Patient Records

The hospital database implements constraints to ensure that each patient record has a unique identifier and appointment dates are valid and fall within working hours. Referential integrity between patients, doctors, and appointments ensures consistent scheduling and avoids orphan records. 

4. Inventory Management

A warehouse management system uses constraints to prevent negative stock levels and duplicate SKU numbers. Foreign keys link inventory items to suppliers and categories, while check constraints validate price ranges and reorder thresholds (the minimum number of products available in stock before the system triggers a reorder).

5. Social Media Platform

A social media database uses constraints to ensure that users’ posts reference existing user accounts, timestamps are valid, and usernames are unique. Even if a user tries to send a friend request to deleted accounts by manipulating the system (like editing the URL or using a script), constraints work silently in the background to prevent friend requests to non-existent users. Constraints also restrict posting the same content more than once.

In brief, constraints in DBMS help to ensure data integrity, consistency, and accuracy with various tables because they enforce rules on the data stored in a table. Such different types of constraints as primary keys, foreign keys, unique, not null, and check constraints should be taken into account, along with their role in avoiding invalid data insertion.

To effectively apply the constraints, it is important to examine the business requirements in detail before implementation. Validating the data at the database level rather than relying on application logic, and striking a balance between the strictness of constraints and system performance, are also key procedures for successful implementation.

Need a more structured and reliable database system? At Webandcrafts (WAC), we help businesses create well-organized data architectures that follow strict rules and ensure long-term stability. Reach out to our team to build a system that fits your needs and ensures a great user experience.