MS Access 2003 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

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8


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. You are allowed to enter duplicate values in the foreign key column.

Click on the Tools item on the menu bar and then click the Relationships option from the expanded menu. This will open up the ‘Show Table’ window. Make sure the ‘Tables’ tab is selected.

Select all the tables in your database that you would like to be in your Primary-Foreign Key relationship and finally click <OK> button. Suppose you have selected two tables; hiring_company and hiring_mgr. In hiring_company we have company_id 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.

Once you perform this kind of drag and drop operation, you will see an ‘Edit Relationships’ window popping up. 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.

Now you would be able to see 1 and infinity sign ( ) and a line linking two tables. 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

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8

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