Relational Integrity
- Mainly security and integrity of a database are the most important factors in judging the success of the system.
- Integrity constraint is a mechanism to prevent invalid data entry into the table to main data consistency.
- The column does not accept values of any other data type.
- Constraints are ed to enforce limits ti? the range 1 of data or type of data that can be inserted/updated/deleted from a table.
- The whole purpose of constraints is to maintain the data integrity during the various transactions
• Domain
Domain Integrity Constraints
- The domain constraints are considered been as the most basic form of integrity constraints. To t, he attributes a domain of permitted values 1s associated.
- For attribute, it defines the default value, the range value, or specific vaJue. '
- The domain integrity constraints are easy to test
when data is entered
- The domain integrity constraints check whether the attribute. having prQp'.er and the right value in the database or not •/
- Domain integrity means )i is the collection of
1. not null
unique
default
check
Data Type
- A domain is the set of all unique values which are
permitted for an attribute.
- Domain constraints are user-defined data types. As we say that domain is the set of unique values, the column for which Domain. The COnstraint has set been. contains the same type of data. based on its data type
1 NOT NULL
- By setting the NOT NULL constraint we can assure that a columildoes· does not hold a NULL v ue.
- When for a specific column, no value is provided while inserting a record into a table, by default" I ( takes NULL value.
- NULL constraint is applied 19 avoid insertion- of any null value in the specific column.
Example
Consider table student having 'name' field WithNOT NULL constraint
2 UNIQUE
- UNIQUE Constraint as the name sugge_Sts,jt·caiJ_ take only unique ·Va1uei fo·:. a 'column' - r of columns. It keeps uniquert¢ss of the table.
- When a column has a·_.uniqUe constraint. then-that particular coh{mn cannot. have duplicate ·valuesJD it.
- Example: We can set the UNIQUE Consiraini for emp_id column m employee table, each employee should have a different emp_id which means this column cannot have duplicate values.
3 DEFAULT
When a user does not provide a value to the column while inserting the records in the table, the DEFAULT constraint provides a default value to that column.
Example
We can set DEFAULT Constraint by assigning the value 10000 to the column exam_fees in the student table. So, if the user doesn't give any value for that column, it takes the default value is 10000.
4 CHECK
This constraint is used to set user-defined constraints for the column. As per the requirements of the business. for which we are·developing the application, updating-"data on a specific field.
Entity Integrity Constraints
1 Primary Key Constraint
- Under Entity Integrity Constraint Primary key is the main factor.
- Primary key uniquely identifies each record in a table. It must have a unique valid. ies and cannot hold null values i.e. Primary key i_s the combination of NOT NULL & UNIQUE constraints.
Referential Integrity Constraint
1 Foreign Key
- The Foreign key con train is also known as l Referential Integrity Constraint. [n this constraint one field is common in between two tables.
- Foreign key represent relationships between tables. There is a parent-child relationship between two tables having a common column.
The master table can be referenced as a parent While the transaction table is considered as a child. The common field will work as the primary key in the parent table while the foreign key is in the child table.
- Example: Consider Training Institute Database having two tables Course_details and Student. There is a condition that the students may register for courses that are available in the institute currently and not for the courses which are not offered at the moment. To specify this rule while inserting values into the database foreign key constrain are used. As follows :
In both tables, the field COURSE_CODE is common. In Cou see details COURSE_CODE is referred to as the primary key and in the STUDENT table it
I. Primary key uniquely identifies a record in the table.
1. Foreign key is a field in the table that is -
primary key in another
table.
2. Primary Key can accept null values
2. Foreign keys can accept null values. is referred to as a foreign key.
Now if we try to delete any record from the master table COURSE_DETAILS, then it will show an error and force us to delete all corresponding records from the child table student.
3. By default, Primary key is clustered index and data m the database table IS
physically organized m the sequence of the clustered index.
3. Foreign keys do not automatically create an index, clustered or non•clustered. You can
manually create an index on a foreign key.
- But in case of on delete cascade rather than showing an error, all the corresponding records from child table STUDENT will get automatically
4. We can have only one Primary key in a table.
4. We can. have more than one foreign key in a table.
On Delete Cascade
If a record in the parent table is deleted then the corresponding records m the child table will automatically be deleted. This is called a delete cascade.
Example
- Consider Training Institute Database having two tables Course_details, and Student.
-There is a condition that the students can register for courses that are available in the institute
currently and not for the courses which are not offered at the moment. To specify this rule foreign
In both tables, the field COURSE_CODE is common. In Cou see details COURSE_CODE is referred to as the primary key and in the STUDENT table it is referred to as the forging key
Now if we try to delete any record from the master table COURSE_DETAILS, then it will show an error and force us to delete all corresponding records from the child table student.
But in case of on delete cascade rather than showing an error, all the corresponding records from the child table STUDENT will get automatically deleted when the record from the parent table COURSE_DETAILS is deleted.
4 Enterprise Constraints
- Enterprise Constraints are also referred as Semantic constraints. They are additional rules specified by users or database administrators.
- These rules are depending upon the requirements and constraints of the business for which the database system is being maintained.
- -For Example, In College System: a class can have a maximum of 30 students. A teacher can teach a maximum of 4 classes a semester.
- In Corporate System an·.employee cannot take a part in more than 5 projects. Salary of an
- an employee cannot exceed the salary of the employee's manager etc.
- Some other examples of business rules·are :
0 A class can have a maximum of 35 students.
the key constraint is used.
0 A course can be .taught many times,· but by
-Not all teachers teach classes, etc.
Comments
Post a Comment