Relational Databases | Relationships
Use a copy of the PK from the one side of a relationship as a FK in the many side of a relationship. You can do this either manually, or by using the lookup wizard to modify the properties of the FK in the table itself.
Primary and Foreign Keys
A relationship requires the primary key of one table be inserted as a foreign key in a second table.
The link between the two like attributes is the relationship.
- tables created manually do not have set relationships
- primary and foreign keys should share the same name and must share the same datatype
- after you have established a relationship, then you will only need to enter data in one table for the related tables to be updated with the new data. This will become more clear in queries which create dynamic tables of data.
[top]
Types of relationships
one-to-one (unusual)
In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A.
one-to-many (most common)
- in a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A
- note that your table properties provide you advice about how to ensure the relationships are based on the proper field properties.
many-to-many (less common than one-to-many, but essential in many cases)
- in a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A
- in Access, this type of relationship is only possible by defining a third table (called a junction table)
[top]
Creating a relationship in Access
You have tables and you have the potential for a relationship, but until you actually link the tables together, the relationship is not yet established
Establish the relationship in the relationship window.
Once you have decided which way the relationship goes, create a copy of the Primary Key from the one side of the relationship and place it as Foreign Key in the many side of the relationship
- you may need to do some work on the Foreign Key properties to ensure the field is repeatable in the table that is the many side of the one-to-many relationship.
-
you accomplish this be selecting the Primary Key with the left mouse key
and then dragging the PK field to the Foreign Key field in the related table and then dropping it there
- a relationship will appear in the Relationship window between the two tables
- a lot of stuff is going on in the background in Access, but for our purposes, the creation of that line between the two fields establishes the relationship between them
[top]
When you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables.
[top]