Foreign Keys & Creating Tables

1. One-to-one

As we have seen in prior tutorials, foreign keys are used to establish relationships between tables. Typically, we do use a primary key in one table and a foreign key in another table to create a one-to-one or a one-to-many relationship between those two tables.

In a one-to-one relationship, one table would serve as a parent table and the other table serves as a child table. With a foreign key constraint, a record must exist in the parent table before a related record can be added to the child table. Basically, a record in the child table has to have a related record in the parent table.

An example of this could be an Employee and a Benefits table. In the Employee table, we would have information specific to the employee such as their name, address, position, and date hired. In the benefits table, you could have the investment plan, medical plan, dental plan, and life insurance plan information. In this scenario, it would not make sense to have the benefit data entered first as it is dependent on the employee. It would be illogical as well to have a record in the benefit table that is not related to a record in the employee table.

The foreign key would be placed in the benefit table to reference the primary key of the Employee table. We would take the primary key in the parent table which is employee in this case and take a copy of the employee_id to use as a foreign key in the benefit table. This is where the term foreign key comes from as the child table would have a primary key of its own but the primary key that we are introducing from the parent table (Employee) is foreign to the child table (Benefit).

We can also see this one-to-one relationship in a set of tables that we have created previously with the representative and department tables:

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL );

CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT, constraint fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id) );

A single representative as a manager is associated with only one department and a single department is associated with only one manager. Interestingly, this is one case that you could make the argument that the department of the representative table could be the parent table. This is since both representatives and departments can exist without the other in place. For example, we could have a department that had recently lost its manager. It does not mean that we also must remove the department as it still exists even though the manager is gone. Likewise, if we created a new department and do not have a manager yet, it should still be able to exist. So you could make the argument that the following relationship is also valid:

CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL );

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL, department_id INT, constraint fk_ department FOREIGN KEY (department _id) REFERENCES department (department _id) );

2. One-to-Many

The one-to-many relationship is similar to the approach that we take with the one-to-one relationship when it comes to foreign keys. However, it is clearer which table is the parent table and which table is the child table. Let us take a look at the Artist and Album table in our database. The one-to-many relationship, in this case, would follow the same guideline where we take the copy of the primary key from the table on the “one” side of the relationship or the parent table. Then incorporate it into the table on the “many” side or the child table. In our case, that will be the artist_id that is the primary key in the Artist table and the foreign key in the Album table.

This should be the case for every one-to-many relationship that that primary key of the “one” side will be a foreign key in the “many” table. It should never be the case that we have a foreign key on the “one” side of a one-to-many relationship.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.