MS Access 2007 Step By Step - Asim Abbasi
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Chapter 2: MS Access Local & Linked Tables
How to Create a Foreign Key?
Primary Key of parent table is the Foreign Key of the child table. This
you should remember always. Now let’s see the step by step process on
defining the Primary-Foreign Key relationship in between tables. Another
thing you should remember i.e. its allowed to enter duplicate values in
the foreign key column.
Figure 2-8: Relationships command lies under the Database Tools.
________________________________________
Click on “Database Tools” as shown in Figure 2-8 and then click the “Relationships” command on the ribbon. This will open up the ‘Show Table’ window as shown in Figure 2-9. Make sure the ‘Tables’ tab is selected and select all the tables in your database that you would like to be in your Primary-Foreign Key relationship and finally click <OK> button.
Figure 2-9: Select the tables for Primary-Foreign key relationships.
________________________________________
Let’s suppose you have selected two tables; “Products” and “Order Details” as shown in Figure 2-10. In Products we have ProductID as primary key. To develop Primary-Foreign Key relationship, all you have to do is to drag the primary key column on to the column you want to make as foreign key.
Figure 2-10: ProductID in “Products” is a Primary Key whereas ProductID
in “Order Details” is Foreign Key.
________________________________________
Once you perform this kind of drag and drop operation, you will see an
‘Edit Relationships’ window popping up as shown in Figure 2-11. Over
here if you check the ‘Cascade Delete Related Records’, you would be
able to delete records from the parent table even if there are
corresponding records in the child table. When you check this option, MS
Access automatically first delete all the related records from the child
table(s) and then delete the parent record(s).
On the other hand if you select the ‘Cascade Update Related Fields’, any
update operation on the primary key column will be having a ripple
effect on the child table(s). In other words all the corresponding child
tables’ foreign key column value(s) will get updated. Finally click the
<OK> button once done.
Figure 2-11: Edit Primary-Foreign Key Relationship.
________________________________________
Now you would be able to see 1 and infinity sign (
) and a line linking
two tables as shown in Figure 2-10. If you want to access the “Edit
Relationships” window again just double click on the line joining 1 and
infinity sign ( ). Table having the 1 digit sign means or represents a
primary key column whereas the other represents the foreign key column.
Duplicates are allowed in foreign key column whereas like explained
before duplicates are not allowed in primary key column.
Chapter 2: MS Access Local & Linked Tables
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Share with others: