3.10- Referential Integrity Constraints DBMS- Integrity Constraints In Relational Data Model | DBMS
3.10- Referential Integrity Constraints DBMS- Integrity Constraints In Relational Data Model | DBMS
PLAYLIST:
DBMS - Data Base Management System Tutorials In Hindi | GATE- NET- DBMS Lectures in Hindi
• DBMS Complete Syllabus- All Universit...
dbms tutorials,
keys in dbms,
database keys,
database management system tutorial,
database tutorials,
dbms gate lectures in hindi,
ugc net dbms lectures in hindi,
gate dbms tutorials in hindi,
ugc net dbms tutorials in hindi,
gate dbms tutorials,
ugc net dbms tutorials,
#gatedbmslecturesinhindi,
#ugcnetdbmslecturesinhindi,
#gatedbmslectures,
#ugcnetdbmslectures,
Integrity Constraints
These are the rules or constraints applied to the database to keep data stable, accurate or
consistent. To keep database consistent we have to follow some rules known as integrity
rules or integrity constraints.
1. Entity Integrity Rule (Integrity Rule 1)
Primary key or a part of it in any relation cannot be null. Suppose A be the attribute in relation R which is taken as primary key then A must not be null.
Employee
EID Name Salary Department
1 Amit 6,000 Accounts
2 Sumit 10,000 Computer
3 Lalit 15,000 Accounts
4 Deepak 9,000 Electrical
5 Sandeep 4,000 Civil
FIGURE 4.23. Integrity rule 1.
2. Referential Integrity Rule (Integrity Rule 2)
A foreign key can be either null or it can have only those values which are present in the primary key with which it is related.
Suppose A be the attribute in relation R1, which is also the primary key in relation R2, then value of A in R1 is either null or same as in relation R2.
148 Introduction to Database Management System
Not allowed because age must be greater than 18 Not allowed because
salary has integer data type Null value is allowed
Dept-ID Dept-Name
1A Accounts
2C Computer
3E Electrical
4C Civil
This is not allowed because Dept-ID is a foreign key and the value 4F is not present in attribute Dept-ID of relation Department.
Employee Department
EID Name Salary Dept-ID
1 Amit 6,000 1A
2 Sumit 10,000 2C
3 Lalit 15,000 4F
4 Deepak 9,000 3F
5 Sandeep 4,000 —
FIGURE 4.24. Integrity rule 2.
3. Domain Constraints
The restrictions which we applied on domain are known as domain constraints. These restrictions are applied to every value of attribute. By following these constraints you can keep consistency in database. These restrictions include data types (integer, varchar, char, time format, date format etc.), size of variable, checks (like value not null etc.) etc.
Ex. create table employee
(Eid char (4),
Name char (20),
Age integer (2),
Salary integer,
primary key (Eid),
Check (age greaterthan 18))
Employee
EID Name Age Salary
1 Aditya 22 10,000
2 Dinesh –18 5,600
3 Sumit 25 8,000
4 Lalit 20 ABC
5 Gaurav 23 11,000
FIGURE 4.25. Domain constraints on relation employee.
4. Key Constraints In any relation R, if attribute A is primary key then A must have unique value or you can say that primary key attribute must have unique value.
Duplicate values in primary key are invalid.
Data Models 149
Not allowed
(Duplicate tuple)
Invalid
Ex. Employee
EID Name Age
1 Aditya 22
2 Sumit 19
3 Deepak 25
2 Manoj 24
4 Dheeraj 28
FIGURE 4.26. Key constraints on relation Employee.
5. Tuple Uniqueness Constraints
In any relation R, all tuples in relation R must have distinct values. In other words
Duplicate tuples within a single relation are not allowed.
Ex. Employee
EID Name Age
1 Aditya 22
2 Sumit 19
3 Deepak 25
2 Sumit 19
FIGURE 4.27. Tuple uniqueness constraints on relation employee.
Example. Consider the two relations given below
A B C D A F
a1 b1 c1 d1 a1 f1
Null b2 Null d1 a2 Null
a1 b1 c1
Relation R Relation S
Given that A is the primary key of R, D is the primary key of S and there is a referential integrity between S.A and R.A. Determine all integrity constraints that are violated.
Sol.
– The primary key of R contains the NULL value and the value ‘a1’ is duplicated. This violates the entity integrity constraint in the relation R.
– In the primary key of S, the value ‘d1’ is duplicated. This violates the entity integrity constraint in the relation S.
– The foreign key S.A contains the value ‘a2’. This value is not available in the parent key R.A. This violates the referential integrity constraint in the relation S. Notes Link:
http://www.tutorialsspace.com/Downloa...