From the course: Developing Microsoft SQL Server 2016 Databases

Understand data integrity - SQL Server Tutorial

From the course: Developing Microsoft SQL Server 2016 Databases

Start my 1-month free trial

Understand data integrity

“

- [Narrator] Storing data in a database is a worthless endeavor, if you cannot implicitly trust that the values meet certain standards of quality, completeness, and validity. The realm of data integrity provides a number of tools to help insure that your data remains trustworthy as records get added, updated and removed. Enforcing data integrity throughout the database will require taking advantage of many different techniques, depending on the situation and the business rules that your system needs to support. For instance, you might have a a rule that employees must be 18 years old or older. With a domain integrity check, you can insure that all of your employees meet this requirement at the point their information is being added to the system. Domain integrity is focused on data specifications applied at the column level of a table. And won't allow records to be added if they violate the rules. One of the top level lines of defense against unruly data is the concept of entity integrity. Entity integrity refers to the fact that each item in the database should be represented by only one record. It's possible that your organization might employ two people with the same name, but there should never be two records in the database for the same person. Entity integrity is commonly controlled through the use of UNIQUE constraints, where the system simply won't allow duplicate values to get saved. By doing so, you can prevent two employees from getting assigned the same ID number by accident. Another tool in the data integrity arsenal is something called referential integrity. This method maintains the relationships between tables to keep things synced. If a record in a primary key or a parent table is removed, what happens to all of the foreign key or child values in another table? For instance, a customer is deleted, but all of their order history persists in the database. If you do nothing, then these records become orphaned with no connection to the rest of the system. Perhaps the order details should get removed as well. Or maybe assign them to another customer account. The realm of referential integrity provides options for handling all of these situations, and it's up to you to determine the best course depending on your unique situation. By making appropriate use of some of the many data integrity tools available within SQL's server, you can guarantee that the data being stored remains trustworthy and of a high quality.

Contents