Oracle - SQL Table Basics - Table Relationships

Create a foreign key constraint

On the create table page we created three tables, person, phone and address. These three entities have the following cardinal relationship to one another.

Logical table relationship

The relationship between two tables can be expressed in meaningful terms. In our design it is possible for a person to have many addresses. For example, we can have a home address, a work address, and of course our summer home in the Hamptons :-}. A person can also have more than one phone number. As you can see our database design would seem to fit a  "real world" utilization of these three tables. The design also provides the user flexibility in defining how many addresses and phone numbers they wish to store. Logical diagrams are essential and oftentimes omitted from a database design. They are essential because they clearly and graphically communicate our table relationships. 

Physical table relationship

The physical diagram depicts the primary key and foreign key relationship between our entities. Our primary table is "person". The foreign key column is the column "person_num" in both the address and the phone tables respectively. 

At present all we have are three separate tables we have created. These tables currently only have a semantic relationship to one another. We now need to create foreign key constraints in order to establish a bone-fide relationship between our three tables.

Creating a foreign key constraint

The sole purpose of a foreign key constraint is to define a relationship between two tables.

Creating the PERSON - ADDRESS foreign key constraint

ALTER TABLE ADDRESS ADD 
      CONSTRAINT FK_ADDRESS_PERSON_NUM
      FOREIGN    KEY (PERSON_NUM)
      REFERENCES PERSON(NUM)
/

Creating the PERSON - PHONE foreign key constraint

ALTER TABLE PHONE ADD 
      CONSTRAINT FK_PHONE_PERSON_NUM
      FOREIGN    KEY (PERSON_NUM)
      REFERENCES PERSON(NUM)
/  

By adding these constraints the DBMS now understand the table relationships. 

Understanding Cardinal Relationships

Knowing how foreign key constraints work allow us to understand how tables relate to one another. Some people really struggle with this area. There is no need to struggle armed with a little bit of pertinent knowledge.

As long as we can determine the primary key of each of our tables, and what the foreign key relationship is we can understand the following.

• What is the relationship between two tables (one to many, one to one)
• If the relationship is one to many we can make an assessment as to how much data can be stored on the "many" side.
• We can begin to understand and appreciate the difficulties and many challenges faced in designing relational databases.

Determining One to Many table relationships

Honing in first on the phone tables relationship to the person table we can gather the following information.

Primary Key (PK) person table: num

Primary Key (PK) phone table: person_num , type_code

Foreign Key relationship between person and phone:  person.num = phone.person_num

Now lets take a look at a picture that shows us some actual data between our two tables. This visualization will really will help us understand the relationships between two tables.

Upon examination of the picture above we can clearly see through representation of the data the one to many relationship. We can also see that in the phone table that foreign key column represents only part of the primary key of that table. Since the goal of a primary key is to guarantee uniqueness of the row we can also determine the number of phone numbers that can be stored for a given person into this table. This will be predicated on the use of the type_code column. If you recall, the type_code column is 3 charactors which allows us a great deal of flexibility. For example, we could store more than one work phone for a person (WK1, WK2, etc...). We can also make a general statement regarding one to many table relationships that will hold true for any two tables we decide to create and relate to one another.

Primary Key (PK) - Foreign Key (FK) LAW #1: If the foreign key column is part of the primary key (or part of an index) then the relationship between the tables will be one to many (1:M).

You can use similar logic to determine the relationship between the person table and the address table.

Determining One to One table relationships

Our examples do not show one to one relationships. But we can also make a general statement regarding one to one table relationships that will hold true for any two tables we decide to create and relate to one another.

Primary Key (PK) - Foreign Key (FK) LAW #2: If the foreign key column represents the entire primary key (or the entire index) then the relationship between the tables will be one to one  (1:1).

What would we need to do to create a one to one relationship between the person table and the address table?

If you answered change the primary key of the address table to be only the column person_num that is correct.

But what would be a key drawback of doing this?

If you answered we would only be able to store one address per person you would be correct as well.