Ms Access 2010 tutorial: Primary Foreign key Table relationships


Primary Foreign key Table relationships

XI. Creating Table Relationships

If you create a database to manage something, the database must contains at least two tables.  In addition, the two tables must have relationship to each other. To know what is relationship, how to create relationship and the type of relationship read following points.

XI.1. What Is Relationship?

A relationship of two tables occurs through a primary key of a table. This primary key becomes the foreign key of another table.

XI.2. What Is Primary Key

Primary Key is a key that you set on a field of the table to prohibit the duplicate value in the table. It also has the role to create the relationship between the tables. The relationship cannot happen if the main table does not have primary and the child table do not contain foreign key.

Microsoft Access 2010 primary key 

XI.3. What Is Foreign Key?

Foreign key is a primary key field of a table that exists to another table.

Microsoft Access 2010 foreign key 

XI.4. Type of Relationship

There are three types of table relationship include one to one, one to many, and many to many relationship.

- One-to-one relationship: A relationship that each record in the first table can have only one matching to each record of the second table and vice versa. This relationship happens in case you define it. For example, an employee can have only one legal wife. From this example, you can create two tables:

- TblEmployee(EmpID, EmpName, Sex, DOB, Address, Phone, SpouseID)

- TblSpouse(SpouseID, SpouseName, Sex, DOB, Phone)

The two tables relate to each other through SpouseID fields. SpouseID in the TblEmployee is a foreign key referencing to the SpouseID primary key in the TblSpouse.

Note: EmpID and SpouseID represent primary keys of the tables.

You can match one-to-one relationship between the two table as the figure below:

Microsoft Access 2010 one to one relationship 

Remark: To create one-to-one relationship, you must set indexed on the SpouseID foreign key by selecting Yes(No Duplicates).

- One-to-many relationship: In this relationship, each record in the first table can match to multiple records in the second table. For instance, a publisher can publish many books, but one book can be published by only one publisher. Through this example, you can create two tables:

- TblPublisher(PubID, PubName. Addr)

- TblBook(BookID, PubID, Title, ISBN)

The two tables relate to each other through PubID fields. PubID in the TblBook is a foreign key referencing to the PubID primary key in the TblPublisher.

You can match one-to-many relationship between the two table as the figure:

Microsoft Access 2010 one to many relationship 

- Many to many relationship: A relationship that each record in the first table can match to many records in the second table and each record in the second can match to many records in the first table. For example, one student can study in a room or many rooms and one room can contain one student or many students. In this case, you can create two tables:

- TblStudent(StudentID, StudentName, Sex, DOB, Address, Phone)

- TblRoom(RoomID, RoomName, Floor)

In many-to-many relationship, only two tables are not valid to create a relationship. You need to create the third table to connect the two tables. The third table calls TblRoomStudent that contains the foreign keys of the two tables.

- TblRoomStudent(StudentID, RoomID)

You can match many-to-many relationship between the three table as the figure:

Microsoft Access 2010 many to many relationship 

 




Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.