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.
In a Nutshell
Constraints in DBMS ensure the integrity and accuracy of the data. The main types of constraints include Domain Constraints, Key Constraints, Entity Integrity Constraints, Referential Integrity Constraints, and Tuple Uniqueness Constraints.
What Are Constraints 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.
Time to strengthen your DBMS with key constraints?
Let's talkLoading...
Types of Constraints in DBMS with Examples
Constraints at a Glance
Constraint Type | Purpose |
---|---|
Primary Key | Uniquely identifies each row. Enforces entity integrity. |
Foreign Key | Maintains referential integrity between tables. |
Unique | Ensures values in a column/set are distinct. |
Not Null | Ensures a column cannot have NULL values. |
Check | Enforces custom conditions or rules on column values. |
Default | Sets a default value when no input is provided. |
User-defined | Implements business-specific rules using CHECK or triggers. |
Informational | Business rules are known to developers but not enforced by the DBMS. |
Domain | Restricts attribute values to a specific set/type. |
Key Constraint | Any attribute(s) that uniquely identify a tuple (e.g., Primary/Unique keys). |
Entity Integrity | Ensures the primary key is unique and not null. |
Referential Integrity | Ensures foreign key values match existing primary keys. |
Tuple Uniqueness | Ensures no duplicate rows using primary or unique keys. |
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
StudentID | FirstName | LastName | DateOfBirth |
S1 | Emy | John | 2000-01-02 |
S2 | Dan | George | 2010-09-05 |
S3 | Jeny | Fransis | 2020-02-03 |
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:
LessonID (PK) | LessonName | Credits |
CS11 | Programming | 4 |
MA22 | Mathematics | 5 |
PH33 | Physics | 4 |
SQL
CREATE TABLE Lessons (
LessonID VARCHAR(10) PRIMARY KEY,
LessonName VARCHAR(100) NOT NULL,
Credits INT
);
Enrollments:
EnrollmentID (PK) | StudentID (FK) | LessonID (FK) | LessonID (FK) EnrollmentDate |
1 | S1 | CS11 | 2025-01-05 |
2 | S1 | MA22 | 2025-01-05 |
3 | S2 | PH33 | 2025-01-09 |
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
UserID (PK) | Username (Unique) | Email (Unique) |
U1 | john_joe | john.joe@test.com |
U2 | jose_jim | jose.jim@test.com |
U3 | george_ben | NULL |
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
ProductID (PK) | StockQuantity | PricePerUnit |
P1 | 200 | 20.99 |
P2 | 0 | 15.50 |
P3 | 100 | 5.00 |
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
ContactID (PK) | FirstName (NN) | LastName (NN) | PhoneNumber | |
11 | Ann | Joe | 111-222-3333 | ann.joe@test.com |
22 | Ben | Joseph | NULL | ben.joseph@test.com |
33 | Mark | Christo | 098-765-4321 | NULL |
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
OrderID (PK) | CustomerID | OrderDate | Status |
W1 | C1 | 2024-02-10 | Pending |
W2 | C2 | 2024-02-11 | Completed |
W3 | C1 | 2024-02-12 | Pending |
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
AppointmentID (PK) | PatientID | DoctorID | AppointmentDateTime | DurationMinutes |
A1 | P1 | D1 | 2024-02-10 09:00:00 | 30 |
A2 | P2 | D2 | 2024-02-10 10:30:00 | 45 |
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
StaffID (FK) | Quarter | PerformanceRating |
11 | 2024-Q1 | Excellent |
12 | 2024-Q1 | Good |
11 | 2024-Q2 | Excellent |
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
ReadingID (PK) | SensorType | Value | Unit | ReadingTimestamp |
R1 | Temperature | 20.5 | Celsius | 2024-05-10 08:00:00 |
R2 | Humidity | 65.2 | Percent | 2024-05-10 08:05:00 |
R3 | Pressure | 1010.5 | hPa | 2024-05-10 08:10:00 |
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)
LessonID (FK) | Semester | Year | InstructorID (FK) |
CS11 | Fall | 2024 | F1 |
MA21 | Fall | 2024 | F2 |
CS11 | Spring | 2025 | F3 |
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
);
Types of Integrity Constraints
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
StaffID | FirstName | LastName | |
100 | Ann | Sam | sam@one.com |
200 | Ben | John | john@one.com |
300 | Cyril | Bob | bob@one.com |
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:
DivisionID | DivisionName |
A | HR |
B | IT |
C | Sales |
SQL
CREATE TABLE Divisions (
DivisionID INT PRIMARY KEY,
DivisionName VARCHAR(100) NOT NULL UNIQUE
);
Staff Table (with Foreign Key):
StaffID | FirstName | LastName | DivisionID |
100 | Ann | Sam | A |
200 | Ben | John | B |
300 | Cyril | Bob | C |
400 | Dan | Leo | NULL |
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
ProductID | ProductName | SKU |
11 | PC | PC-11 |
22 | iPhone | IPH-22 |
33 | Headset | HD-33 |
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
);
Real-World Use Cases
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.
Conclusion
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.
Ready to power up your DBMS with essential constraints?
Let's talkLoading...
Discover Digital Transformation
Please feel free to share your thoughts and we can discuss it over a cup of tea.