Data Integrity Considerations

Data Integrity

Data integrity refers to the accuracy, reliability, and strength of data over its lifecycle. Data with “integrity” is said to have a complete structure and the characteristics defining the data must be correct. Data integrity covers data in storage, during processing, and while in transit.?

Data integrity is lost in various ways such as when inaccurate data is entered, if data is not appropriately updated, if data gets corrupted during transmission from one computer to the other, or if data is erroneously deleted.

For data to be valuable, it is imperative to maintain the integrity of data at all times.

Here is an image showing the types of data integrity.

1.1. Entity Integrity

Entity means any person, place, or thing in a database. Entity integrity refers to the process of setting a primary key for each table in a database. In other words, it is used to confirm that the primary key cannot be null. The primary key here is used to identify each record,? thus records with a null primary key can't be identified.

Here is an image showing a record with a null entry, therefore not allowed as the primary key.

1.2. Domain Integrity

A domain is a set of acceptable values a column can include. Domain integrity constitutes a certain set of rules or requirements to define the kind of attribute a column can hold in a database table.?

Here is an image showing an example of domain integrity where the indicated value is out of the domain because it's not an integer.

1.3. Referential Integrity

Referential integrity ensures that there must always exist a reasonable relationship between two relational database tables. This reasonable relationship between the two relational database tables confirms that a foreign key exists on the table.

Here is an image showing the value 1 which is referential integrity because it is included in the primary table and the value 15, which is not because it is not included in the primary table.

1.4. User-defined Integrity

User-defined integrity refers to the rules and requirements set by the user to fit their particular needs. Sometimes the other integrity types are not enough therefore the user can set more requirements to work with.

Here is an image showing Academy003 not following the user-defined integrity because it does not contain “After” like the other entries.

2. Integrity Constraints

Integrity constraints are used to secure the consistency and accuracy of data in any relational database. Integrity constraints are pre-defined sets of rules to maintain the data integrity of the table.

Here is an image showing the types of integrity constraints.

2.1. Unique Integrity

The unique integrity ensures that any value on a table is unique. Unique integrity is a rule that forbids duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints.

2.2. Check Integrity

Check Integrity is a method that checks the distribution and structural integrity of all the objects in a given database. This is significant for the maintenance of the database.

2.3. Primary Key?

A primary key is used to confirm that the data in a respective column is unique. Databases use the primary keys to compare, sort, and store records, and to build relationships between records. An example of a primary key is a student ID number.

Here is an image showing an example of the primary key, where every student has a unique studentId.

2.4. Foreign Key?

A foreign key is a column or columns of data in one table that directs to the primary key data in another table. Foreign keys correlate together with two or more tables in a relational database.

Here is an image showing how the foreign key acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.