Integrity constraints in DBMS | Domain, Entity, Referential, Key Constraints
Summary: You will learn in this article, Integrity constraints in DBMS such as Domain Integrity Constraint, Entity Integrity Constraint, Referential Integrity Constraint, Key Constraints. These types of Constraints in SQL play an important role, Let’s understand Integrity constraints in DBMS for example. I hope you will enjoy it!
Introduction to Integrity Constraints in DBMS
- Integrity constraints in DBMS are a set of rules that are applied on the table columns or relationships to confirm that the overall validity, integrity, and consistency of the data present in the database table is maintained.
- The integrity constraints ensure that the data, each and every time a table insert, update, delete, or alter operation is performed, it’s evaluated against the terms or rules mentioned in the integrity constraint.
- The data is inserted, updated, deleted, or altered only on the condition that the results of the constraint come out to be True.
- Thus, integrity constraint in DBMS prevents accidental damage or harm to the database by an authorized user.
Types of Integrity Constraint
In relational Database Management Systems (DBMS), we primarily have four types of integrity constraints, namely :
- Domain Integrity Constraint
- Entity Integrity Constraint
- Referential Integrity Constraint
- Key Constraints
In this most important particular article, we will try to learn different types of integrity with the help of a few illustrations:
1) Domain Integrity Constraint
Domain integrity constraints in SQL can be defined as the definition of a valid set of values for an attribute.
The data type of domain includes integer, character, string, time, date, currency, etc. Furthermore, the value of the attribute must be available in the corresponding domain.
A domain constraint is a set of rules that restricts the kind of attributes or values a column or relation can hold in the database table.
i.e. we can specify if a specific column can hold null values or not, furthermore, if the values have to be unique or not, the size of values or the data type that can be entered in the column, the default values for the column, etc.
|
1 2 3 4 5 6 |
CREATE TABLE CustomerDetails ( CustomerID character (255) NOT NULL, CustomerName character (255) NOT NULL, Quantity integer NOT NULL, DatePurchased date ); |
|
1 2 3 |
INSERT INTO CustomerDetails( CustomerID, CustomerName, Quantity, DatePurchased) VALUES ('IND222','Aditya Shukla','11', 2019-11-21); |
In the above screenshot, we can see that error is Operand type clash: int is incompatible with date the domain constraint enables the server to throw an error.
Another Example
2) Entity Integrity Constraint
The Entity Integrity Constraint is defined as the PK-primary key value that can not be null.
Therefore, because the primary key (PK) value is used to identify individual rows in relation, and if the primary key has a null value, then we can not determine/identify those rows.
A table will contain a null value apart from the primary key field.
Entity Integrity Constraint is used to ensure or confirm the uniqueness of each record or row in the record table. Furthermore, there are primarily two kinds of integrity constraints that help us in ensuring the uniqueness of each row, namely, the UNIQUE and PRIMARY KEY constraints.
This unique key helps in uniquely identifying a record in the data table.
Thus, it can be considered somewhat identical to the PK-Primary key as both of them guarantee the uniqueness of a record.
However, unlike the primary key (PK), a unique key can accept NULL values and it can be used on more than one column of the data table.
|
1 2 3 4 5 6 7 |
CREATE TABLE Students( Stu_ID int NOT NULL, Stu_Name varchar(255) NOT NULL, Class_Name varchar(255) UNIQUE, Age int, PRIMARY KEY (Stu_ID) ); |
Therefore, let’s try to make an insert SQL query and check if the entity constraints have been successfully applied.
|
1 2 3 |
INSERT INTO Students( Stu_ID, Stu_Name, Class_Name, Age) VALUES (111,'Mohan','S',22),(111,'Pratima','S',24); |
In the above screenshot that when we tried to insert duplicate values, then the entity constraints forced the server to throw errors.
Another Example
3) Referential Integrity Constraint
Referential Integrity Constraint is defined as it ensures that there always exists a valid relationship between two tables.
Furthermore, this makes confirms that if an FK-foreign key exists in a table relationship then it should always reference a corresponding value in the 2nd table or it should be null.
In another word, we can say that a referential integrity constraint is specified between two tables.
Furthermore, we can create relationships between two tables in the given below the following manner. Here, we have created a “Odres” table and then “Customers” where the “Orders” attribute references to Order_ID” in the former table.
|
1 2 3 4 5 |
CREATE TABLE Orders( Order_ID int NOT NULL, Product_Name varchar(255) NOT NULL, PRIMARY KEY(Order_ID) ); |
|
1 2 3 4 5 6 7 |
CREATE TABLE Customers( Customer_ID int NOT NULL, Customer_Name varchar(255) NOT NULL, Orders int NOT NULL, Age int, FOREIGN KEY (Orders) REFERENCES Orders(Order_ID) ); |
|
1 2 3 |
INSERT INTO Customers( Customer_ID, Customer_Name, Orders, Age) VALUES (102,'Kaml Davis',11,34); |
Another Example
Let’s see another example if a foreign key in Table 1 refers to the PK-Primary Key of Table 2, then each value of the FK-Foreign Key in Table 1 must be null or be available in Table 2.
4) Key Constraints
Keys are those entity sets that are used to identify an entity within its entity set uniquely.
An entity set can have multiple keys, but out of which one key will be the PK-primary key.
Furthermore, a PK-primary key can contain a unique & null value in the relational table.
There are the following some of the key constraints in SQL:
Conclusion of Integrity constraints in DBMS
You have learned in this article, Integrity constraints in DBMS such as Domain Integrity constraints, Entity Integrity constraints, Referential Integrity constraints, Key Constraints. Happy Learning! I hope you will enjoy it!.
About Author
