DBMS Integrity Constraints
Maintaining consistent and correct data is essential in the database industry. A Database Management System (DBMS) must prevent invalid or inconsistent data entries that can corrupt relationships or produce unreliable results. This is where integrity constraints come into play.
A collection of guidelines known as integrity constraints are applied to database tables in order to preserve the accuracy, consistency, and legitimacy of the data. They ensure that the data modification operations like insertions, deletions, or updates do not compromise the integrity of the database.
Machine Learning Tutorial:-
Data Science Tutorial:-
Complete Advance AI topics:-
Deep Learning Tutorial:-
What are Integrity Constraints?
Integrity constraints are predefined rules applied to table fields (columns) or relationships between tables in a DBMS. These constraints govern what kind of data is allowed, ensuring only valid and logical information is stored. Any operation that violates these constraints is automatically rejected, thus preventing accidental data corruptioneven by authorized users.
These limitations can be defined in two primary ways:
- Column Constraints: Defined as part of the column definition during table creation.
- Table Constraints: Defined at the end of the
CREATE TABLEstatement and can apply to one or more columns.
Types of Integrity Constraints in DBMS
1. Domain Constraints
The acceptable range of values for a certain attribute is specified by domain constraints. The data type and permissible range of values for a column are specified by the domain. For example, a column storing employee age should not accept a string or a negative number.
Example:
emp_id Name Birth_year Dept_id
10001 Raja 2000 001
10002 Tiya 2001 002
10003 Puja 1999 001
10004 Rohit 2002 B Invalid (Dept_id should be numeric)
Here, the last row violates domain integrity because the Dept_id expects an integer, not a character.
Check Constraint:
Used to enforce domain-level rules by specifying a condition that values must satisfy.
Syntax (Column Level):
column_name datatype CONSTRAINT constraint_name CHECK (condition);
Syntax (Table Level):
CONSTRAINT constraint_name CHECK (condition);
NOT NULL Constraint:
Ensures that a column does not accept NULL values.
Syntax:
column_name datatype CONSTRAINT constraint_name NOT NULL;
2. Entity Integrity Constraints
Each table row can be uniquely identifiable thanks to entity integrity.This is enforced using a Primary Key, which must contain unique and non-null values.
Example:
Roll_no Name Birth_year
41 Akash 2009
23 Mina 2008
14 Rony 2010
NULL Tina 2009 Invalid (Primary Key can't be NULL)
Since the main key field Roll_no is null in this instance, the final row violates the entity integrity constraint.
3. Referential Integrity Constraints
Consistent relationships between tables are guaranteed via referential integrity. It is enforced through foreign keys, which must either be NULL or match a primary key in the referenced table.
Example:
Students Table
Roll_no Name Branch_ID
11 Ronti 3
12 Priya 2
13 Puja 4 Invalid (Branch_ID 4 doesn't exist)
Branch Table
Branch_ID Branch_Name
1 CSE
2 EE
3 ME
In this example, Branch_ID 4 in the Students table breaks the referential integrity because it doesnt exist in the Branch table.
4. Key Constraints
Rows in a table can be uniquely identified with the use of key constraints.
Primary Key Constraint:
Each row is uniquely identified by a primary key, which also prevents duplicate or NULL values.
Syntax (Column Level):
column_name datatype CONSTRAINT constraint_name PRIMARY KEY
Syntax (Table Level):
CONSTRAINT constraint_name PRIMARY KEY (column_name1 [, column_name2])
Example:
emp_id Name Birth_year Dept_id
10001 Raja 2000 001
10002 Tiya 2001 002
10001 Rohit 2002 003 Duplicate emp_id, violates primary key
Unique Key Constraint:
While allowing NULL values, a unique constraint guarantees that every value in a column or group of columns is unique.
Syntax (Column Level):
column_name datatype CONSTRAINT constraint_name UNIQUE
Syntax (Table Level):
CONSTRAINT constraint_name UNIQUE (column_name1 [, column_name2])
Primary Key vs Unique Key: Key Differences
| Aspect | Primary Key | Unique Key |
|---|---|---|
| Number of keys per table | Only one | Multiple allowed |
| NULL values | Not allowed | Allowed |
| Purpose | Uniquely identifies each row | Ensures uniqueness for non-primary data |
Complete Python Course with Advance topics:-
SQL Tutorial :-
Download New Real Time Projects :-Click here
Conclusion
Integrity constraints are essential for preserving the quality and consistency of data in a DBMS. They serve as a protective framework that enforces rules on data values and relationships, preventing errors and ensuring meaningful data handling.
The four primary types of DBMS integrity constraintsDomain Constraints, Entity Integrity, Referential Integrity, and Key Constraintswork together to ensure that data entered into a system is both valid and reliable.
By understanding and implementing these constraints properly, organizations can significantly improve their data integrity and make their database systems more robust and trustworthy.
Stay tuned with for more insightful articles on databases and technology.
integrity constraints sql
integrity constraints in dbms
referential integrity constraints
referential integrity constraints in dbms
integrity constraints in dbms geeksforgeeks
integrity constraints in dbms types
integrity constraints in dbms with example
entity integrity constraints
acid properties in dbms
normalization in dbms
integrity constraints in database
integrity constraints example
