Data integrity refers to the consistency and accuracy of the data. It is typically enforced by the procedures and guidelines in the database design phase. In RDMS, keys enforce data integrity. A key is user-defined and forces values in a table to conform to a specified standard. This standard will allow only certain kinds of values to be in the database.

Data integrity refers to the consistency and accuracy of data and table relationships. The following table lists the types of integrity you can use:

Each type of integrity and how each relates to one another is discussed in the following sections.

To ensure that each row in a table is identifiably unique, you use entity integrity. This is done with a few different types of keys or constraints, including unique, not null, and primary key constraints.

To ensure that all values in a column or columns are different from each other, you use a unique constraint. This type of key can be applied to any data type and is used to avoid duplicate data. You can apply a unique constraint to multiple columns so that it creates a unique value across those multiple columns. It can contain null values.

If you create a unique constraint on one column, it will force the table to have unique values in that specific column. If they are not unique, then the row will not be able to be inserted or updated.

In the following screenshot, the parkkey constraint is unique. All the other fields can have duplicate information as long as the parkkey unique constraint isn't violated:

If you create a unique constraint on a combination of columns in a table, it will force the table to have unique values in the combination of those columns in the unique constraint. If they are not unique, the row will not be able to be inserted or updated.

The following screenshot shows an example of a composite, unique constraint. In this case, playerID, yearID, and teamID would need to be unique for the row to be acceptable:

To ensure that all values in a column are not null, you use a not null constraint. This type of key can be applied to any data type and is used to avoid missing data. If you create a not null constraint on a column, it will force the table to have values in that specific column. If the values are null, then the row will not be inserted or updated.

In the following screenshot, you can see that the birthYear constraint is set to not null. The deathYear constraint would allow nulls since not all people have a year of death:

The primary key is used to ensure that all values in a column are not null and unique. This key combines the unique and not null constraint properties into one key. This type of key can be applied to any data type and is used to avoid missing and duplicate data. You can only have one primary key per table.

If you create a primary key on a table, it will force the table to have unique, not null values in that specific column. If the values don't comply, then the row will not be able to be inserted or updated. You can also create a primary key on multiple columns. This is considered a composite key. In this case, the composite key would have to be unique for each row, otherwise the row could not be inserted or updated.

In the following screenshot, the playerID constraint would be the primary key because it's unique and not null for every row in the table:

In the following screenshot, the playerID, yearID, and teamID constraints could be the composite primary key because the combination of those three columns is unique and not null for every row in the table:

Referential integrity refers to the consistency and accuracy between tables that can be linked together. By having a primary key on the parent table and a foreign key on the child table, you achieve referential integrity. A foreign key on the child table creates a link between one or more columns in the child table and the primary key on the parent table. When a foreign key is present, it must reference a valid, existing primary key in the parent table. This way, the data in both tables can maintain a proper relationship. You will learn more about this in the following example.

If you don't set up referential integrity, you wind up with orphaned records. For example, let's say that you delete a player from the first table here:

Now let's say that you didn't delete the corresponding record in the second table here. In this case, the second table's records would be orphaned:

If there was a foreign key constraint on the salary column, then the player could not be deleted from the parent table without first deleting the corresponding salary rows in the salary table. By having a foreign key constraint, we will also prevent users from adding rows to the child table without a corresponding parent row or changing values in a parent table that would result in orphaned child table records.

You won't get an error if there is incomplete data when you lack referential integrity constraints. It's basically like your records are lost in the database since they may never show up in reports or query results. This can cause all kinds of problems, such as strange results, lost orders, and potentially life-and-death situations where (for example) patients don't receive proper treatments.

When creating a foreign key constraint, the foreign key must reference a column in another table that is the primary key. It can be any data type and accept duplicate and null values by default. The foreign key constraint can maintain three types of table relationships (covered in more detail in Chapter 7, Querying Multiple Tables):

• One-to-one: This type of relationship is when one table has just one corresponding row in another table. An example of this could be a table with employees and computers. Each employee has one computer.
• One-to-many: This type of relationship is when one table has none, one, or many corresponding rows in another table. An example of this could be a table with adults and children. An adult table row may have none, one, or many rows in the child table.
• Many-to-many: This type of relationship is when many rows in one table correspond to many rows in another table. An example of this could be the customers and products tables. Customers can purchase many products.

In the following screenshots, the primary key would be on the first table as playerID. The second table would have a foreign key reference to playerID on the first table. In this case, there would be a one-to-many relationship between the first and second tables because there is one player in the first table and none, one, or many rows corresponding to that player in the second table.

If you had a foreign key setup on playerID in the second table, then you would not be able to delete the playerID value from the first table unless you deleted it in the second table beforehand. This key setup maintains the referential integrity and ensures that you won't have orphaned records in the second table:

To ensure that data values follow defined rules for formatting, range, and value using check and default constraints, you use domain integrity.

The check constraint is used to ensure that all values in a column are within a range of values. This type of key can be applied to any data type and is used to ensure that values aren't invalid. A check constraint is enforced with user-defined conditions and evaluates as either true or false. You can define a check constraint on a single column or a combination of columns in a table.

The following screenshot shows an example of a table where a check constraint would make sense on the inducted column. A player can either be inducted into the hall of fame or not. In this case, you could create a check constraint that only allows Y or N in that field. If the value isn't Y or N, then the row can't be updated or inserted:

The following screenshot shows an example of a table where a check constraint can be applied to multiple columns. For instance, you wouldn't want deathYear to be a year before the birthYear, so you can set a check constraint that will only allow you to add or update a birthYear or deathYear that follows a check constraint like birthYear < deathYear:

To ensure that all rows in a column have a value, you use a default constraint. This type of key can be applied to any data type. A default constraint assigns a default value to a field. This is used to avoid having a null value for a field if a user doesn't specify a value.

The following screenshot shows an example of a table where a default constraint could make sense on the ab column:

A player can be in a game without having any at-bats. In this case, you could create a default constraint that sets the ab column to 0 if the user provides no value.