Data Integrity
Data integrity is the existence of accurate and consistent data in the database, and it can be maintained by data constraints.
Data constraints are integrity rules that limit the types of data that can go into a column or table within a database system.
- goal of constraints overall is to provide high-quality data
- means it is correct and entered in accurately
- is a must for data integrity
Types of Data Integrity Rules
Domain integrity is the acceptable values for a field.
- e.g., the appropriate data type is a data constraint
Entity integrity is the unique identifier of a record as defined using a primary key field.
- e.g., if the primary key field cannot allow a null value, this is a constraint that ensures all records can be uniquely identified
Referential integrity is established to maintain that records are not orphaned by ensuring the proper table has the key field.
- refers to the integrity of data between two tables
User-defined integrity integrity based on business rules that are covered by the other data integrity settings.
- use logic that goes beyond a setting, to constrain to a data type or format
- e.g., suppose an organization has products in their system that do not have a specified color because they are multicolor
- When the data entry occurs, logic can be defined that when these products are entered, the field for color will default to multicolor
- data entry person does not have to attempt to guess what color option to apply, nor do they even have to spend time keying that information in
- ensures we have exactly the right information expected in that field