Database Analysis. Database Design. Extended DB Features. Oracle SQL Extensions. The power of relational databases stems from the fact that tables can be linked , enabling users to gain access to data across tables. Let us briefly examine how links are formed between tables. Select the field or fields that you want to define as the primary key. To select one field, click the row selector for the desired field. To select multiple fields, hold down the Ctrl key, and then click the row selector for each field.
If you want the order of the fields in a multiple-field primary key to differ from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey. Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data.
You can set referential integrity when all the following conditions are true:. For relationships in which referential integrity is enforced, you can specify whether you want Access to automatically cascade update or cascade delete related records. If you set these options, delete and update operations that would usually be prevented by referential integrity rules are enabled.
When you delete records or change primary key values in a primary table, Access makes the necessary changes to related tables to preserve referential integrity. 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.
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 has no effect because you cannot change the value in an AutoNumber field. 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, 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 that are related to the "Orders" records. When you delete records from a form or datasheet when the Cascade Delete Related Records check box selected, Access warns you that related records may also be deleted.
However, when you delete records by using a delete query, Access automatically deletes the records in related tables without displaying a warning. Option 1 defines an inner join. An inner join is a join in which 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 side. 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 side.
Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy. Skip to main content.
This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Note Novice: Requires knowledge of the user interface on single-user computers. Note You can create relationships not only in tables but also in queries.
Note If you want the order of the fields in a multiple-field primary key to differ from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey. Note If the primary key in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box has no effect because you cannot change the value in an AutoNumber field. Note that the many-to-many relationship is defined differently when your data is stored in a geodatabase.
In general, if you have relationship classes defined in your geodatabase, you should use these instead of creating new ones in ArcMap. Learn how to create a relationship class. A relationship class stores information about associations among features and records in a geodatabase and can help ensure your data's integrity. Learn more about the benefits of using relationship classes. Follow these general guidelines when choosing between joins and relates on your data:.
Learn more about deciding between relationship classes, joins, and relates. When you save a map containing joins and relates, ArcMap saves the definition of how the two attribute tables are linked rather than saving the linked data itself. The next time you open your map, ArcMap reestablishes the relationship whether a join or relate between the tables by reading the tables from the database.
In this way, any changes to the source tables that have taken place since they were last viewed on the map are automatically included and reflected on the map. Joins can be stored in an ArcMap document or in a layer file. If you plan on moving the data at some point, you should save your ArcMap documents with relative paths.
If data is moved, you can repair tables and layers after opening the document, but unless the target table and the join tables are in the same directory or workspace, the joins are not repaired. If you save your document with relative paths, tables and layers are restored automatically with joins as long as the document has been moved relative to where the data has been moved. You can make a permanent disk copy of a layer with joined data simply by exporting the layer. To export the layer, right-click it in the table of contents, point to Data , then click Export Data.
This creates a new feature class with all the attributes, including the joined fields. If your data is involved in both joins and relates, the order in which the joins and relates are created is significant. If your layer or table has a relate, it is removed once data is joined to it. If you perform a relate on a joined layer or table, the relate is removed when the join is removed.
As a general rule of thumb, it is best to create your joins, then add your relates. In a situation where you need to join tableA and relate tableB to layerC, two of the three possible ways of doing this will work. The following describes each case:. Relates owned by the join table are not affected by the join. These relates can't be accessed by the target table or layer, however.
0コメント