| Lesson 3 | Data integrity |
| Objective | Describe the elements of data integrity. |
Database Data Integrity Elements
Because the sole purpose of SQL Server is to handle data, data integrity is very important. Data integrity refers to the quality of data within your entire database. Data integrity problems occur when SQL Server tables are not linked together correctly, such as when a value in one table attempts to relate to the same value in another table, but the second value has been deleted.
Enforcing data integrity
Enforcing data integrity is an essential function of your database software, and is accomplished through a combination of entity, relational, domain, and user-defined integrity.
What is entity integrity within a Database?
Entity integrity refers to the concept that each row in a database table must have a unique identifier, known as a primary key. This ensures that no two rows in the table can have the same primary key value, and thus be distinguished from one another. This is important for maintaining the accuracy and consistency of the data in the database.
Entity integrity means enforcing the quality of data within a specific table. Entity integrity is enforced primarily by one or more of the following three relational concepts:
| Primary key | A combination of one or more columns that uniquely identifies a row in a table. |
| Unique constraint | One or more columns that cannot contain duplicate values within the rows of data across the column(s) that make up the constraint. SQL Server 2012 enforces constraints by automatically creating a unique index. |
| Identity flag | An attribute of a column in a table that is used to automatically generate unique values for that column. |
Relational integrity
Once you have guaranteed the quality of your data within the table (entity integrity), you are ready to implement relational integrity and domain integrity.
Relational integrity guarantees the quality of the data between tables by enforcing relationships between them. Relational integrity is sometimes referred to as referential integrity. Relational integrity is enforced through foreign keys.
A foreign key consists of one or more columns in a table that relates to a primary key, or unique key in another table.
Domain integrity
Domain integrity enforces valid values for a column within a table. Domain integrity is enforced by check constraints. A check constraint is a user-defined rule that dictates valid values for columns in a table.
User-defined integrity
As an SQL Server 2012 user, you can define methods for insuring integrity that are specific to your organization. These methods are known as business rules. User-defined integrity will be discussed later in this course, in the context of rules, triggers, and stored procedures. In the next lesson, you will learn about business rules and how they define your data model.