| Lesson 3 || Data integrity |
| Objective || Describe the elements of data integrity. |
Data Integrity Elements of a Database
Because the sole purpose of SQL Server 2008 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 7 tables aren't 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.
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. |
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 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.
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.