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. Data integrity is a cornerstone of SQL Server since its primary role is to manage and store data reliably. When tables aren't linked correctly, or when referential integrity breaks down (like in the example you mentioned where a value in one table references a deleted value in another), it can lead to a cascade of issues: inconsistent queries, unreliable reports, or even application errors. SQL Server provides several mechanisms to maintain data integrity, like:

1. Primary Keys and Foreign Keys: Ensuring each row in a table is unique (via primary keys) and that relationships between tables (via foreign keys) are valid. For instance, if a foreign key in a "Orders" table references a "Customers" table, SQL Server can enforce that the customer ID exists before allowing the order to be inserted.
2. Constraints: Things like NOT NULL, UNIQUE, or CHECK constraints help enforce rules at the data level. For example, a CHECK constraint could ensure a column like "OrderDate" isn’t set to a future date if that’s a business rule.
3. Cascading Actions: To handle your example of a deleted value, SQL Server supports cascading updates or deletes. If a customer is deleted, you could configure the foreign key to automatically nullify or delete related orders, preventing orphaned records.
4. Transactions: Wrapping operations in a transaction ensures that either all changes (like updating multiple related tables) succeed, or none of them do, avoiding partial updates that break 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 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.

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.