Saturday, 17 August 2013

DB2 Relationships.

If records in a given table can be associated in some way with records in another table, the tables are said to have a relationship between them. The manner in which the relationship is established depends on the type of relationship. Three types of relationships can exist between a pair of tables: one-to-one, one-to-many, or many-to-many.

One-to-One
A pair of tables is related one-to-one when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table. In this type of relationship, one table is referred to as the primary table, and the other is referred to as the secondary table. The relationship is established by taking the primary key of the primary table and inserting it into the secondary table, where it becomes a foreign key. This is a special type of relationship because in many cases the foreign key also acts as the primary key of the secondary table.

One-to-Many
When a pair of tables has a one-to-many relationship, a single record in the first table can be related to many records in the second table, but a single record in the second table can be related to only one record in the first table. This relationship is established by taking the primary key of the table on the “one” side and inserting it into the table on the “many”side,where it becomes a foreign key.

Many-to-Many
A pair of tables is in a many-to-many relationship when a single record in the first table can be related to many records in the second table, and a single record in the second table can be related to many records in the first table. In order to establish this relationship properly, you must create what is known as a linking table.
This table provides an easy way to associate records from one table with those of the other and will help to ensure that you have no problems adding, deleting, or modifying any related data. You define a linking table by taking a copy of the primary key of each table in the relationship and using them to form the structure of the new table. These fields actually serve two distinct roles: Together they form the composite primary key of the linking table, and separately they each serve as a foreign key.




Created with Artisteer

No comments:

Post a comment