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
What is Primary & Foreign Key?
Primary key is basically a constraint. If defined on any column/field
you would not be able to insert anything which is already there in that
particular column.
To establish a link between two tables or in other words to have
Primary-Foreign Key relationship between two or more tables, it is
imperative to have primary key defined on at least one of the tables.
Primary key of one table acts as the foreign key in other table. A
foreign key column can have duplicate values. Once you establish a
Primary-Foreign Key relationship between two or more tables it’s like
having a constraint on all the tables in the relationship. Such
constraint is called “Referential Integrity” constraint. The table
having primary key column is called Parent table all the other tables
linked to the Parent table via foreign key are called child tables.
Once this Primary-Foreign key relationship is defined in between tables,
the following constraints get effective.
• You cannot delete any record from the parent table if there are
corresponding record(s) in the child table(s).
• You cannot insert any record in the child table if there is no
corresponding record in the parent table.
Let’s look at the example to completely understand the concept behind
these constraints. Consider one table having name as ‘suppliers’ with
the following data in it.
The second table having the name as ‘orders’ with the following data in it.
Once you have defined a primary-foreign key relationship between these
two tables with supplier_id of ‘suppliers’ table as primary key and
supplier_id of ‘orders’ table as foreign key, if you try to delete
supplier_id record 10001 from suppliers table, MS Access will not let
you do so. To delete such type of records you have to first delete all
the corresponding records from the child table(s). Only then you would
be able to delete the record from the parent table.
There is only one condition though when MS Access allows you to delete
under such circumstance, you have to explicitly use the “Delete with
Cascade” option. We will show you how later.
In the second scenario, if you try to insert a record in ‘orders’ table
with supplier_id as 99999, MS Access will not allow you to do so. The
only possible way to make such record entry is to first add the record
with supplier_id as 99999 in ‘suppliers’ table.
Now let’s have a look on how to create Primary and Foreign keys in MS
Access.
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: