Download
1 / 15
150 likes | 169 Views
Learn about the different types of table relationships in Microsoft Access and how they ensure data consistency and security. Explore the concepts of one-to-one, one-to-many, and many-to-many relationships, and discover how to enforce referential integrity.
E N D
TABLE RELATIONSHIPS • A database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table while making data entry more secure by use of relationships. • To prevent the duplication of information in a database by repeating fields in more than one table, table relationships can be established to link fields of tables together. • Certain types of relationships may exist between one table and another.
TABLE RELATIONSHIPS • There are three main types of relationships that can be defined: • ONE-TO-ONE: In this relationship, a record in the primary (parent) table has a single corresponding record in the related (child) table where both of the related columns are primary keys or have unique constraints. For example, you may have a table containing employee addresses, while another table has employee phone numbers information. There should be one record in the primary table related to EXACTLY one record in the related table. This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to: • Divide a table with many columns. • Isolate part of a table for security reasons. • Store data that is short-lived and could be easily deleted by simply deleting the table. • Store information that applies only to a subset of the main table.
TABLE RELATIONSHIPS • ONE-TO-MANY: This is the most common type. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. • MANY-TO-MANY: A row in table A can have many matching rows in table B, and vice versa. This relation can be created by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. Authors table and Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table.
TABLE RELATIONSHIPS • Relationships can be defined by using the Relationships button. • While the Edit Relationships window is active, the checkbox below the list allows you to enforce referential integrity. • This means you CANNOT enter a record into the related table (in this case, Transactions) without a correct Customer ID from the primary table. (Customers)
TABLE RELATIONSHIPS • The following rules apply when you use referential integrity: • You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. You cannot have an order that is assigned to a customer that does not exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field. • You cannot delete a record from a primary table if matching records exist in a related table. You cannot delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table. • You cannot change a primary key value in the primary table, if that record has related records. For example, you cannot change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.
TABLE RELATIONSHIPS • For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update or cascade delete related records. • If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. • When you delete records or change primary key values in a primary table, Microsoft Access makes the necessary changes to related tables to preserve referential integrity.
TABLE RELATIONSHIPS • If you click to select the Cascade Update Related Fields check box when you define a relationship, any time that you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship is not broken. • Microsoft Access cascades updates without displaying any message. • If the primary key in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.
TABLE RELATIONSHIPS • If you select the Cascade Delete Related Records check box when you define a relationship, any time that you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). • When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query, Microsoft Access automatically deletes the records in related tables without displaying a warning.
TABLE RELATIONSHIPS • There are three join types, as follows: • Option 1 defines an inner join. An inner join is a join where records from two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match. • Option 2 defines a left outer join. A left outer join is a join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right. • Option 3 defines a right outer join. A right outer join is a join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left.
TABLE RELATIONSHIPS • Click the Relationships button on the toolbar. • From the Show Table window (click the Show Table button on the toolbar to make it appear), double click on the names of the tables you would like to include in the relationships. When you have finished adding tables, click Close.
TABLE RELATIONSHIPS • To link fields in two different tables, click and drag a field from one table to the corresponding field on the other table and release the mouse button. • The Edit Relationships window will appear. • From this window, select different fields if necessary.
TABLE RELATIONSHIPS • Check the Enforce Referential Integrity box to ensure that the relationships are valid and that the data is not accidentally deleted when data is added, edited, or deleted. • From the same window select an option for Enforce Referential Integrity if necessary. These options give Access permission to automatically make changes to referential tables if key records in one of the tales is deleted. • Click Create to create the link.
TABLE RELATIONSHIPS • A line now connects the two fields in the Relationships window.
TABLE RELATIONSHIPS • The datasheet of a relational table will provide expand and collapse indicators to view subdatasheets containing matching information from the other table. • To expand or collapse all subdatasheets at once, select Format| • Subdatasheet| • Expand All or Collapse All from the toolbar .
