Constraints are a very important feature in a relational model. The relational model actually backs up the clearly defined theory of constraints on attributes or tables. Because they enable a designer to define the semantics of data in the database, constraints are useful. Rules known as constraints compel DBMSs to verify that data complies with semantic requirements.
A constraint of the relational model, domain limits the values of the attributes in the relationship. There are, however, some real-world semantics for data that are impractical to specify when only used with domain constraints. More precise language is required to specify which data values are permitted or not, as well as the format that is appropriate for an attribute. The Employee ID (EID) must be unique, for instance, or the Employee Birthdate must fall between January 1, 1950, and January 1, 2000. Such information is provided in logical statements called integrity constraints.
Every table must have a primary key in order to guarantee entity integrity. The PK and its components cannot contain any null values. This is because rows with null values for the primary key cannot be identified. For instance, the primary key for the EMPLOYEE table cannot be Phone because some individuals may not own a phone.
A foreign key must match a primary key or be null to maintain referential integrity. This constraint maintains the correspondence between the rows in the two tables (parent and child) between which it is specified. It indicates that a row in one table must have a valid reference in another table.
The PK and FK must share the same data types and be from the same domain when configuring referential integrity; otherwise, the relational database management system (RDBMS) won’t permit the join. The relational model introduced by Edward O. Wilson, also known as RDBMS, is a widely used database system. F. Codd of IBM’s San Jose Research Laboratory. Compared to other database systems, relational database systems are simpler to use and comprehend.
What is referential integrity?
Referential integrity is the idea that every reference from one table to another must be valid in order for any changes to one table to have an impact on data that is linked to it. You can achieve this by using a series of procedures that ensure your data is stored and handled consistently across tables. These procedures are built into the database’s structure to make sure that any modifications, additions, or deletions of data won’t jeopardize the integrity of data in other areas of the database. These guidelines help to ensure that data is accurate and pertinent while preventing you from accidentally duplicating data.
A database’s referential integrity reveals whether the data is reliable. Two tables with customer contact information and sales reports would serve as an illustration of this. When you updated a customer’s information in the contact information table of a database with referential integrity, the database also updated the same information in the sales reports table. This prevents errors that may occur when entering the same information more than once in different places.
What is data integrity?
The overall accuracy of the data in a database is known as data integrity. The limitations of a database’s data storage capabilities determine the data’s integrity. A database’s design incorporates a set of procedures and guidelines for data entry to establish the parameters. No matter how long you store it for or how frequently you access it, as long as you follow the parameters, the data can continue to be accurate and complete. The term “data integrity” can also refer to how protected the data is within a database against unauthorized access or manipulation.
A rule in the table or database design that mandates that all numbers be positive whole integers is an illustration of a procedure that ensures data integrity. This can prevent confusion and ensure uniformity across all data. However, a database can have many different types of data integrity. Here are two different types of data integrity:
Physical integrity
Physical integrity is the defense of data stored against any circumstances that make it impossible to retrieve data. Natural disasters, power outages, and hacking are examples of circumstances that compromise physical integrity. These occurrences jeopardize physical integrity because you can’t access the information. Despite not being specific physical events, human error or storage erosion can still prevent you from accessing the database and retrieving the data, making them a challenge for physical integrity.
Logical integrity
Data must make sense given its context within the database in order for it to be considered to have logical integrity. Logical integrity requires data to be accurate, complete and unchanging. In order to maintain the logic of the data, it also depends on safeguards against human error and hackers rather than on accessibility. Logical integrity comes in three different flavors, in addition to referential integrity:
Similarities between referential integrity and data integrity
There are some similarities between the two concepts because referential integrity is a component of data integrity:
Accuracy
The accuracy of the data you’re storing is referred to by the terms referential integrity and data integrity. Both have procedures built into their software that ensure the data is accurate so the database can continue to use it and manipulate it. A working database needs accurate data because it can’t reliably store data without it.
Risk
Referential integrity is a component of data integrity, so some of the risks are shared by both. One of the biggest risks is human error. Data integrity can be compromised if someone enters information without following the correct procedures. They risk losing referential integrity in particular if they tamper with any of the references between tables.
The data integrity of your database can also be impacted by additional error types:
Constraints
You are limited in how you can store data in order to develop procedures that guarantee accurate data. You could, for instance, make a table listing the names of those invited to a holiday party and another table referencing those names to indicate the gifts you are getting for each person. You cannot record a gift you intend to give to someone you didn’t invite to the holiday party in the second table if your database has referential integrity. The list of people receiving gifts is constrained by the party guest list in the first table.
Similar restrictions that restrict how you enter data can be necessary for data integrity. Data must typically be entered using the same format and style to enable references and avoid data duplication or corruption. Although these restrictions can make your databases more difficult, they ultimately protect your data, and you can work around them to keep your database’s data integrity.
Differences in data integrity and referential integrity
Here are some differences between data integrity and referential integrity:
Breadth
Referential integrity is just one part of data integrity. Along with the additional forms of integrity mentioned above, data integrity also includes the following ideas:
Focus
Procedures for ensuring data consistency and referential integrity concentrate specifically on the relationship between tables. Data entry and retrieval are the main subjects of data integrity rules. Although both are necessary for the database to operate properly, they occur at different times. When entering data, common data integrity rules, such as the rule stating that all data must be positive integers, occur. Referential integrity checks occur when constructing relationships between tables.
Compliance
Data integrity is essential for adhering to laws that shield businesses from penalties brought on by careless handling of their customers’ personal data. In addition to the Federal Trade Commission Act, which mentions data protection on a federal level, the US, for instance, has a variety of regulations used to protect data depending on the state. While referential integrity is a component of data integrity, it has no bearing on the security of personal data.
11 03 referential integrity part1
FAQ
What is data referential integrity?
Referential integrity refers to the relationship between tables. A primary key is required for each table in a database and can appear in other tables as a result of its connection to the data in those other tables.
What is the difference between referential and domain integrity?
A value in one table must refer to an existing value in another table for there to be referential integrity. According to the referential integrity rule, a foreign key’s value must either be null or fall within the scope of the related primary key. The set of legal values for any column is known as a domain.
What are the four types of data integrity?
- Domain Integrity.
- Entity Integrity.
- Referential Integrity.
- User-Defined Integrity.
Which is an example of referential integrity?
Referential integrity entails that a row’s reference to another table in one table must be valid. In the company’s Customer/Order database, examples of referential integrity constraints include Customer(CustID, CustName) and Order(OrderID, CustID, OrderDate).
