Relationships that are 1:1 do not require referential integrity constraints.
|
The purpose of this article is to provide best data quality management practices for creating a database with referential integrity. Show
Referential integrity is a term used in database design to describe the relationship between two tables. It is important because it ensures that all data in a database remains consistent and up to date. It helps to prevent incorrect records from being added, deleted, or modified. Referential integrity is a constraint on the database design that makes certain that each foreign key in a table point to a unique primary key value in another table. This will ensure that data is not lost, and it will also help you maintain data quality. What are the Causes of Inconsistent Database Data?Referential integrity is violated when there is more than one primary key value for a foreign key. If referential integrity is not enforced, then it may lead to data inconsistency and data loss. The following are some of the reasons why this constraint is violated: → Primary keys are not properly enforced → Foreign keys are not properly enforced → Database design is incorrect. Why do we Have Referential Integrity in the First Place?Referential integrity is a data quality concept that ensures that when you make changes to data in one place, those changes are reflected in other related records. This is done by enforcing a rule that says that the foreign key in one table can only refer to the primary key of another table. This means that if you change the information in one column, it will automatically be updated in all other related columns. A primary key constraint violation is a constraint where the primary key of the table referenced cannot be null. Best Practices for Creating Databases with Referential IntegrityReferential integrity is usually enforced by creating a foreign key in one table that matches the primary key of another table. If referential integrity is not enforced, then you may encounter data redundancy and inconsistencies. The first step to creating a database with referential integrity is to identify all tables and their respective keys. You can do this using the data quality tools within the iData toolkit. The next step would be to decide what type of relationship exists between these tables. There are three types of relationships: One-to-One, One-to-Many, and Many-to-Many. Once you have decided on the type of relationship, you can then create the appropriate relationships between your tables, again using iData. The following are some best practices for creating referential integrity: → Create primary and foreign keys for each table → Ensure that the data types are matching → Ensure that there are no duplicate entries → Make sure to not create circular relationships. What is Database Normalization?Database normalization is a process for organizing the data in a relational database, so that it is easy to query and easy to update. The data is stored in separate tables to avoid data redundancy and improve efficiency - and for this reason, database normalization and referential integrity are closely linked, as referential integrity also ensures that updates are applied consistently across multiple tables. There are three steps to database normalization: 1) First Normal Form - The first step of database normalization is called first normal form. It states that all columns (attributes) must be atomic and cannot be broken down further. 2) Second Normal Form - The second step of database normalization is called second normal form. This step requires that all non-key attributes must be dependent on the key attribute. 3) Third Normal Form - The third and final step of database normalization is called third normal form. It states that all non-key attributes must depend on two or more key attributes. Why Checking your Database Normalization & Referential Integrity is Vital in 2022Database normalization and referential integrity are required to ensure that the data is organized in a way so that it can be accessed and used by any user. Because referential integrity is fundamental to the way in which data is connected in a relational database, it is a vital component before any transformation, such as an ERP or PMS migration. Data needs to be connected so that it can be joined, or linked, and so that changes to one piece of data automatically propagate throughout the system. Foreign key constraints (also known as referential constraints or referential integrity constraints) enable definition of required relationships between and within tables. For example, a typical foreign key constraint might state that every employee in the EMPLOYEE table must be a member of an existing department, as defined in the DEPARTMENT table. Referential integrity is the state of a database in which all values of all foreign keys are valid. A foreign key is a column or a set of columns in a table whose values are required to match at least one primary key or unique key value of a row in its parent table. A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is true:
To establish this relationship, you would define the department number in the EMPLOYEE table as the foreign key, and the department number in the DEPARTMENT table as the primary key. shows how a record with an invalid key is prevented from being added to a table when a foreign key constraint exists between two tables:Figure 1. Foreign and primary key constraints  The table that contains the parent key is called the parent table of the referential constraint, and the table that contains the foreign key is said to be a dependent of that table. Referential constraints can be defined in the CREATE TABLE statement or the ALTER TABLE statement. Referential constraints are enforced by the database manager during the execution of INSERT, UPDATE, DELETE, ALTER TABLE, MERGE, ADD CONSTRAINT, and SET INTEGRITY statements. Referential integrity rules involve the following terms: Table 1. Referential integrity termsConceptTermsParent keyA primary key or a unique key of a referential constraint.Parent rowA row that has at least one dependent row.Parent tableA table that contains the parent key of a referential constraint. A table can be a parent in an arbitrary number of referential constraints. A table that is the parent in a referential constraint can also be the dependent in a referential constraint.Dependent tableA table that contains at least one referential constraint in its definition. A table can be a dependent in an arbitrary number of referential constraints. A table that is the dependent in a referential constraint can also be the parent in a referential constraint.Descendent tableA table is a descendent of table T if it is a dependent of T or a descendent of a dependent of T.Dependent rowA row that has at least one parent row.Descendent rowA row is a descendent of row r if it is a dependent of r or a descendent of a dependent of r.Referential cycleA set of referential constraints such that each table in the set is a descendent of itself.Self-referencing tableA table that is a parent and a dependent in the same referential constraint. The constraint is called a self-referencing constraint.Self-referencing rowA row that is a parent of itself. The purpose of a referential constraint is to guarantee that table relationships are maintained and that data entry rules are followed. This means that while a referential constraint is in effect, the database manager guarantees that for each row in a child table that has a non-null value in its foreign key columns, a row exists in a corresponding parent table that has a matching value in its parent key. When an SQL operation attempts to change data in such a way that referential integrity becomes compromised, a foreign key (or referential) constraint could be violated. The database manager handles these types of situations by enforcing a set of rules that are associated with each referential constraint. This set of rules consist of:
When an SQL operation attempts to change data in such a way that referential integrity will be compromised, a referential constraint could be violated. For example,
The database manager handles these types of situations by enforcing a set of rules that are associated with each referential constraint. This set of rules consists of:
Insert ruleThe insert rule of a referential constraint is that a non-null insert value of the foreign key must match some value of the parent key of the parent table. The value of a composite foreign key is null if any component of the value is null. This rule is implicit when a foreign key is specified. Update ruleThe update rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION and RESTRICT. The update rule applies when a row of the parent or a row of the dependent table is updated. When a value in a column of the parent key is updated, the following rules apply:
The value of the parent unique keys cannot be changed if the update rule is RESTRICT and there are one or more dependent rows. However, if the update rule is NO ACTION, parent unique keys can be updated as long as every child has a parent key by the time the update statement completes. A non-null update value of a foreign key must be equal to a value of the primary key of the parent table of the relationship. Also, the use of NO ACTION or RESTRICT as update rules for referential constraints determines when the constraint is enforced. An update rule of RESTRICT is enforced before all other constraints, including those referential constraints with modifying rules such as CASCADE or SET NULL. An update rule of NO ACTION is enforced after other referential constraints. Note that the SQLSTATE returned is different depending on whether the update rule is RESTRICT or NO ACTION. In the case of a dependent row, the NO ACTION update rule is implicit when a foreign key is specified. NO ACTION means that a non-null update value of a foreign key must match some value of the parent key of the parent table when the update statement is completed. The value of a composite foreign key is null if any component of the value is null. Delete ruleThe delete rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION, RESTRICT, CASCADE, or SET NULL. SET NULL can be specified only if some column of the foreign key allows null values. If the identified table or the base table of the identified view is a parent, the rows that are selected for delete must not have any dependents in a relationship with a delete rule of RESTRICT, and the DELETE must not cascade to descendent rows that have dependents in a relationship with a delete rule of RESTRICT. If the delete operation is not prevented by a RESTRICT delete rule, the selected rows are deleted. Any rows that are dependents of the selected rows are also affected:
The delete rule of a referential constraint applies only when a row of the parent table is deleted. More precisely, the rule applies only when a row of the parent table is the object of a delete or propagated delete operation (defined in the following section), and that row has dependents in the dependent table of the referential constraint. Consider an example where P is the parent table, D is the dependent table, and p is a parent row that is the object of a delete or propagated delete operation. The delete rule works as follows:
Any table that can be involved in a delete operation on P is said to be delete-connected to P. Thus, a table is delete-connected to table P if it is a dependent of P, or a dependent of a table to which delete operations from P cascade. Which of the following is true when representing a 1 1 binary relationship using the relational model quizlet?Which of the following is true when representing a 1:1 binary relationship using the relational model? The key of either entity is placed in the other as a foreign key.
How are one to one recursive relationships addressed using the relational model?How are one-to-one recursive relationships addressed using the relational model? One-to-one recursive relationships are addressed just the same as one-to-one nonrecursive relationships. The only difference is that both of the related entity instances are in the same entity class.
When referential integrity is enforced what are the rules that users must observe?Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid.
What kind of constraint enforces referential integrity between tables?A foreign key constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table.
|
