Domain Constraints in DBMS
Introduction about DBMS:-
DBMS stands for DataBase Management System. DBMS(DataBase Management System) is a type of software by which we can save and retrieve the user's data with the security process. DBMS can manipulate the database with the help of a group of programs. The DBMS(DataBase Management System) can accept the request from the operating system to supply the data. The DBMS also can accept the request to retrieve a large amount of data through the user and third-party software. DBMS(DataBase Management System) also give permission to the user to use the data according to their needs. The word "DBMS" contains information regarding the database program and the users. It also provides an interface between the user and the software. In this topic, we are going to discuss the various type of DBMS.
In DBMS(DataBase Management System), there are four types of constraints available. These are as follows.
- Domain constraint.
- Entity integrity constraint.
- Referential Integrity constraints.
- Key constraints.
In this article, we are going to know about only domain constraints. Let's discuss that.
Domain constraint:-
Domain constraints are a type of user-defined column that helps us to arrange the data we have entered according to the datatype. If we enter a wrong value according to the datatype, then the domain constraint provides an error alert to the user that the column is not fulfilled properly. In another way, we can say that domain constraint is a type of attribute which specify all the possible value that the attributes can hold properly. The attributes that can be held by the values are time, integer, date, character, string, etc. it also defines the set of values for the attributes. And the attribute also ensures that the value taken by the attributes must have the atomic value. Here atomic value means the value cannot be divided from its domain.
Syntax for domain constraint :
The syntax for domain constraint is as below.
Domain Constraint = data type(integer / character/date / time / string / etc.) +
Constraints(NOT NULL / UNIQUE / PRIMARY KEY /
FOREIGN KEY / CHECK / DEFAULT)
Types of Domain Constraint:-
The domain constraint is further divided into two types. These are as follows.
- Domain Constraints – Not Null
- Domain Constraints – Check
1, Domain Constraint-Not Null:-
Here the null value means the value is not assigned with any attributes. We can also say that the null value is the value of the unknown or missing attribute, and the null values are held by the column. But the column has a feature that does not accept any null value, which means that the column must have to be contained some attribute value.
Example: In the employee database, every employee must have a name associated with them.
Create table employee
(employee_id varchar(45),
employee_name varchar(45) not null,
salary NUMBER);
output:-
2. Domain Constraints – Check: It defines a condition that each row must satisfy, which means it restricts the value of a column between ranges, or we can say that it is just like a condition or filter checking before saving data into a column. It ensures that when a tuple is inserted inside, the relation must satisfy the predicate given in the check clause.
Example: We need to check whether the entered id number is greater than 0 for the employee table.
Create table employee
(employee_id varchar(45) not null check(employee_id > 0),
employee_name varchar(45),
salary NUMBER);
output:
The above example creates CHECK constraints on the employee_id column and specifies that the column employee_id must only include integers greater than 0.
Note: In DBMS, a table is a combination of rows and columns in which we have some unique attribute names associated with it. And basically, a domain is a unique set of values present in a table. Let's take an example; suppose we have a table student which consists of 3 attributes NAME, ROLL NO, and MARKS. Now ROLL NO attributes can only have numbers associated with them and won't contain any alphabet. So we can say that it contains only the domain of an integer, and it can be only a positive number greater than 0.
Conclusion
- In a database, a domain is a set of values that can be assigned to an attribute.
- A domain can be created using the CREATE DOMAIN command in SQL.
- There are two types of domain constraints, NOT NULL and CHECK.