Data integrity is the overall completeness, accuracy and consistency of data (Techopedia, n.d.).
In order to achieve data integrity, it is important to analyse Entity and Referential integrity constraints.
Entity Integrity
Focuses on Primary keys.
Each table should have a primary key and each record must be unique and not null.
This makes sure that records in a table are not duplicated and remain intact during insert, update and retrieval.
| ID (pk) (unique) | Name (varchar(255)) | Surname (varchar(255)) |
| 1 | Andrew | Odendaal |
| 2 | John | Smith |
| 3 | Peter | Jones |
Figure 1.1 – Employee table
SELECT * FROM Employees WHERE ID='2'
Referential Integrity
Focuses on Foreign keys.
The state of the foreign key can be binary – two different states – namely; it can refer to a primary key of another table or be equal to null.
If the value is null, then it means that either there is no relationship, or that the relationship is unknown.
Foreign keys are designed to keep relationships between records of a table to records of another table.
| ID (pk) (unique) | Employee_ID (fk) | Name (varchar(255)) |
| 1 | 2 | Project1 |
| 2 | 3 | Project2 |
Figure 1.2 – Projects table
SELECT E.Name, E.Surname, P.Name FROM Projects P, Employees E JOIN P.Employee_ID=E.ID WHERE E.ID='2'
In order to enforce data integrity in a database, one can restrict or “constrain” what is allowed to be inserted, updated or deleted (Sybase, 2011).
For example:
If there are Employees (figure 1.1) and Projects (figure 1.2) tables (as above), one can make sure that each Project is constrained to the Employees table by means of the foreign key so that data, when inserted or updated, will have the correct relationship and each record in the Projects table will point back to a relevant Employee, consistently.
The whole concept around data integrity states that data can be connected to other data.
Data with integrity is said to have a complete, or whole structure (Teeling, 2012).
Most relational databases do not stipulate that each table should have a primary key (Databasedev, n.d.) or that these rules should be applied, however, it is highly recommended and should be considered at very least when designing any database that will require consistent data to be accurately relied on.
References
Techopedia (n.d.) Data Integrity [Online] Techopedia.com, Available from: https://www.techopedia.com/definition/811/data-integrity-databases
Sybase (2011) Enforcing data integrity in databases [Online] Infocenter.sybase.com, Available from: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug300.htm
Teeling, M. (2012) What is Data Integrity? Learn How to Ensure Database Data Integrity via Checks, Tests, & Best Practices [Online] Veracode.com, Available from: https://www.veracode.com/blog/2012/05/what-is-data-integrity
Databasedev (n.d.) Relational Database Entity Integrity [Online] Databasedev.co.uk, Available from: http://www.databasedev.co.uk/entity_integrity.html