To create a relationship in Access, click on the Primary Key in the main table, drag to the Foreign Key in the related table, and let go. Stretch or shrink fieldlists so everything shows A relationship specifies how two tables connect to each other.Īs you build tables in Microsoft Access, or link to tables that are in SQL Server, Excel range, or other database, put them on the Relationships Diagram It is also where relationships are defined. When the requirements are defined and fully understood, the facilitator or analyst can recommend specific referential integrity actions, for instance Restrict or Cascade.The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. Once referential integrity is defined, the facilitator or analyst should test the referential integrity rules defined by the business users by asking questions or working through different scenarios that show the results of the business decision. This provides a method for both capture and display of referential integrity rules. This is a valid business rule in cases where the cardinality is zero, one to zero, or one or more, since instances in the child entity can exist even if there are no related instances in the parent entity.Īlthough referential integrity is not a formal part of the IDEF1X or IE languages, it does capture business rules that indicate how the completed database should work, so it is a critical part of data modeling. For example, a business may decide that referential integrity is not required when deleting an instance in a child entity. Not every action must have a referential integrity rule associated with it. No referential integrity action is required. Set DefaultĮach time an instance in the parent entity is deleted, the foreign key attributes in each related instance in the child entity are set to the specified default value. Set NullĮach time an instance in the parent entity is deleted, the foreign key attributes in each related instance in the child entity are set to NULL. Restrictĭeletion of an instance in the parent entity is prohibited if there are one or more related instances in the child entity, or deletion of an instance in the child entity is prohibited if there is a related instance in the parent entity. Each time an instance of PROJECT is deleted, this Delete cascades to the PROJECT-EMPLOYEE table and causes all related instances in PROJECT-EMPLOYEE to be deleted as well.Īvailable actions for referential integrity include the following: CascadeĮach time an instance in the parent entity is deleted, each related instance in the child entity must also be deleted. The referential integrity option chosen for this action in this relationship is Cascade. The rule that specifies the action taken when a parent key is deleted is called referential integrity.
What would happen if you were to delete an instance of PROJECT? If the business decided that it did not want to track instances in PROJECT‑EMPLOYEE if PROJECT is deleted, you would have to delete all instances of PROJECT-EMPLOYEE that inherited part of their key from the deleted PROJECT. This means that PROJECT-EMPLOYEE is existence-dependent on PROJECT. In addition, the business decides that for each instance of PROJECT-EMPLOYEE there is exactly one instance of PROJECT. The business has determined already that the relationship between PROJECT and PROJECT-EMPLOYEE is identifying, so the primary key of PROJECT becomes a part of the primary key of PROJECT-EMPLOYEE. The action that is applied to the foreign key value varies depending on the rules defined by the business.įor example, a business that manages multiple projects might track its employees and projects in a model similar to the one in the figure below.
If you change a value in a primary key column of a parent table, for example, you must account for this change in each child table where the column appears as a foreign key. Since a relational database relies on data values to implement relationships, the integrity of the data in the key fields is extremely important. Model Relationships › Referential Integrity
0 kommentar(er)
