Domain in DBMS

Constraints:

Constraints in DBMS are a set of guidelines that guarantee that authorized users who modify the database do not alter the consistency of the data. Constraints are expressed in DDL commands like "alter" and "create." There are various kinds of restrictions that are available in DBMS, including:

• domain constraints
• entity integrity constraints
• Reference integrity constraints
• Key constraints

Domain constraints:

User-defined columns called domain constraints allow users to enter values by the data type. Additionally, if it receives an incorrect input, it alerts the user that the column needs to be filled out correctly.

Or, to put it another way, it is an attribute that lists every conceivable value it can take, including character, integer, date, time, string, and so on.

A value taken by an attribute must be an atomic value (cannot be divided) from its domain, which is defined by the collection of values that make up the attribute's domain.

Each table has a certain set of columns and depending on the type of data in each column, the same type of data can be entered. Any other data type cannot be used to fill the column.

• A valid set of values for an attribute can be described as a domain constraint.
• Domain data types include strings, characters, integers, times, dates, currencies, and more. The matching domain must contain the attribute's value.

Type of domain constraints:

1. Not null
2. Check
3. unique

1. Domain Constraints - Not Null:

 By default, a column can contain null values, which are values that have not been assigned, are unknown, or are missing values for an attribute. Now that we are aware that the Not Null constraint only prevents a field from ever having an empty value, you can see that it prevents you from adding a value to a field when adding a new record or updating an existing record.

A column cannot have a NULL value, thanks to the NOT NULL constraint. When adding a record to a table, if we don't specify a value for a certain column, it automatically accepts NULL. We may be certain that a specific column or columns cannot have NULL values by adding a NULL constraint.

Example 1:

Create table employee
(employee_id varchar(30),
employee_name varchar(30) not null,
salary NUMBER);

It will create the employee table with id, name, and salary. The salary should not contain any null values.

Example 2:

Age is a not null

ID	Name	Age
1000	Ram	20
1001	Rani	22
1002	Raju	23
1003	Renu	0

Here it is not allowed because 0 is a null value. So, it violates the domain integrity constraints.

2.Domain Constraints - Check:

It establishes a requirement that each row must meet by limiting the value of a column to a set of ranges, or, in other words, it functions as a condition or filter checking prior to saving data into a column.

It guarantees that when a tuple is entered into the relation, the predicate stated in the check clause must be satisfied.

This constraint is used to define the range of values for a specific table column. This constraint ensures that the specified column's value must fall inside the specified range when it is applied to a column.

Example 1:

Create table employee
(employee_id varchar(30) not null check(employee_id > 0),
employee_name varchar(30),
salary NUMBER);

Example 2:

ID	Name	Age
1000	Ram	20
1001	Rani	22
1002	Raju	23
1003	Renu	a

Here a is integer type so it is violating the check constraint.

3. Domain Constraints - unique:

The SQL unique constraint is used to determine whether the result of the subquery contains duplicate tuples. It provides a Boolean result showing whether duplicate tuples are present or not. Only if there are no duplicate tuples in the subquery does the unique construct return true; otherwise, it returns false.

Important Information

• Evaluates to true when the subquery is empty.
• It only comes back true if the subquery's result contains unique tuples (two tuples are unique if the value of any attribute of the two tupldiffersfer).
• The return value is true if the subquery returns two duplicate rows with at least one attribute being NULL.

Example:

SELECT table.ID
FROM  table
WHERE UNIQUE (SELECT table2.ID
 FROM table2
 WHERE table.ID = table2.ID);