Types of Constraints in DBMS or Relational Constraints in DBMS with Examples-
What are Database Constraints in DBMS ??
Database constraints are restrictions on the contents of the database or on database operations. It is a condition specified on a database schema that restricts the data to be inserted in an instance of the database.
Need of Constraints :
Constraints in the database provide a way to guarantee that :
- the values of individual columns are valid.
- in a table, rows have a valid primary key or unique key values.
- in a dependent table, rows have valid foreign key values that reference rows in a parent table.
Different Types of constraints in DBMS with Example :
- Domain Constraints
- Tuple Uniqueness Constraints
- Key Constraints
- Single Value Constraints
- Integrity Rule 1 (Entity Integrity Rule or Constraint)
- Integrity Rule 2 (Referential Integrity Rule or Constraint)
- General Constraints
Domain Constraints –
Domain Constraints specifies that what set of values an attribute can take. Value of each attribute X must be an atomic value from the domain of X.
The data type associated with domains include integer, character, string, date, time, currency etc. An attribute value must be available in the corresponding domain. Consider the example below –
Tuple Uniqueness Constraints –
A relation is defined as a set of tuples. All tuples or all rows in a relation must be unique or distinct. Suppose if in a relation, tuple uniqueness constraint is applied, then all the rows of that table must be unique i.e. it does not contain the duplicate values. For example,
Key Constraints –
Keys are attributes or sets of attributes that uniquely identify an entity within its entity set. An Entity set E can have multiple keys out of which one key will be designated as the primary key. Primary Key must have unique and not null values in the relational table. In an subclass hierarchy, only the root entity set has a key or primary key and that primary key must serve as the key for all entities in the hierarchy.
Example of Key Constraints in a simple relational table –
Example of Key Constraints in an subclass hierarchy –
Single Value Constraints –
Single value constraints refers that each attribute of an entity set has a single value. If the value of an attribute is missing in a tuple, then we cal fill it with a “null” value. The null value for a attribute will specify that either the value is not known or the value is not applicable. Consider the below example-
Integrity Rule 1 (Entity Integrity Rule or Constraint) –
The Integrity Rule 1 is also called Entity Integrity Rule or Constraint. This rule states that no attribute of primary key will contain a null value. If a relation have a null value in the primary key attribute, then uniqueness property of the primary key cannot be maintained. Consider the example below-
Integrity Rule 2 (Referential Integrity Rule or Constraint) –
The integrity Rule 2 is also called the Referential Integrity Constraints. This rule states that if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2. For example,
Some more Features of Foreign Key –
Let the table in which the foreign key is defined is Foreign Table or details table i.e. Table 1 in above example and the table that defines the primary key and is referenced by the foreign key is master table or primary table i.e. Table 2 in above example. Then the following properties must be hold :
- Records cannot be inserted into a Foreign table if corresponding records in the master table do not exist.
- Records of the master table or Primary Table cannot be deleted or updated if corresponding records in the detail table actually exist.
General Constraints –
General constraints are the arbitrary constraints that should hold in the database. Domain Constraints, Key Constraints, Tuple Uniqueness Constraints, Single Value Constraints, Integrity Rule 1 (Entity Integrity) and 2 (Referential Integrity Constraints) are considered to be a fundamental part of the relational data model. However, sometimes it is necessary to specify more general constraints like the CHECK Constraints or the Range Constraints etc.
Check constraints can ensure that only specific values are allowed in certain column. For example , if there is a need to allow only three values for the color like ‘Bakers Chocolate’, ‘Glistening Grey’ and ‘Superior White’, then we can apply the check constraint. All other values like ‘GREEN’ etc would yield an error.
Range Constraints is implemented by BETWEEN and NOT BETWEEN. For example, if it is a requirement that student ages be within 16 to 35, then we can apply the range constraints for it.
The below example will explain Check Constraint and Range Constraint –
|Database Keys in DBMS||Constraint Violations during
(insert, update, delete)
Incoming search terms:
- constraints in dbms
- relational constraints
- Types of Constraints in DBMS
- key constraints in dbms
- relational model constraints
- integrity constraints in dbms
- types of constraints
- relational constraints in dbms
- integrity constraints in dbms notes
- Domain constraints