Domain Constraints in DBMS

Domain constraints in a Database Management System (DBMS) define the permissible data values for specific attributes within a table. They ensure data accuracy and integrity by enforcing rules like data type, range, and format. Domain constraints in DBMS prevent invalid or inconsistent data from being stored, maintaining the quality of the database.

The Types of Constraints Available in DBMS

The constraints in DBMS, are rules preventing authorized users from compromising data consistency. Specified within DDL commands like "alter" and "create," types include domain, entity integrity, referential integrity, and key constraints.

A. Domain Constraints

Domain constraints in DBMS is a rule that defines the set of valid values for a column in a database table. Domain constraints in DBMS are the most basic type of integrity constraint and are used to ensure that the data in the database is valid. For example, domain constraints in DBMS could specify that the values in a gender column must be either male or female.

Domain constraints in DBMS are enforced by the database management system (DBMS). When a user tries to insert or update a value in a column that violates a domain constraint in DBMS, the DBMS will reject the operation.

Type of domain constraints

1. Domain Constraints – Not Null:

   The NOT NULL constraint is a type of domain constraint in DBMS. It specifies that a column cannot have a null value. This means that every row in the table must have a value for that column. The NOT NULL constraint is used to ensure that the data in the database is complete and accurate.

   Example: The customer_name column in a customer table would typically have a NOT NULL constraint because every customer must have a name. If a user tries to insert a row into the table without a value for the customer_name column, the DBMS will reject the operation.

2. Domain Constraints – Check:

   The CHECK constraint is a type of domain constraint in DBMS. It specifies a condition that all values in a column must satisfy. The CHECK constraint is used to ensure that the data in the database is valid and consistent

   Example: The price column in a product table might have a CHECK constraint that the price must be greater than or equal to 0. This means that a user cannot insert a product into the table with a negative price.

   The CHECK constraint can be used to enforce a wide variety of conditions, such as:

   • The values in a column must be within a certain range.
   • The values in a column must be unique.
   • The values in a column must match a certain pattern.

B. Entity Integrity Constraints

Entity integrity constraints ensure that each row in a table is unique and can be uniquely identified. They are used to ensure the accuracy and consistency of the data in the database.

• In this example, the customer_id column is the primary key of the customers table. This means that the values in the customer_id column are unique and cannot be NULL.
• If a user tries to insert a row into the customers table with a duplicate value in the customer_id column, the DBMS will reject the operation.

C. Referential Integrity Constraints

Referential integrity constraints ensure that the data in one table is linked to the data in another table. They are used to prevent errors and inconsistencies in the data, such as orphan records or duplicate data.

• In this example, the customer_id column in the orders table is a foreign key that refers to the customer_id column in the customers table. This means that the values in the customer_id column in the orders table must match the values in the customer_id column in the customers table.
• The ON DELETE CASCADE option specifies that when a row is deleted from the customers table, all rows in the orders table that reference the deleted row will also be deleted.

D. Key Constraints

Key constraints are a type of constraint that ensures the uniqueness of the values in a column or combination of columns. There are three types of key constraints:

Example:

In this example, the customer_id column is the primary key of the customers table. This means that the values in the customer_id column are unique and cannot be NULL.