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
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
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: