Problem solve Get help with specific problems with your technologies, process and projects.

Composite foreign keys, Part 1

We have a composite primary key in a table, and we have a relationship between this table and another table. What happens when this composite key goes to the other table as a foreign key?

What happens is that the foreign key is composite too. This can be a good or bad thing, depending on your point of view and on the nature of the various queries you'll be running.

Consider the following example:

DIVISION DVID DVNAME ex Executive mk Marketing hr Human Resources it Information Technology
DEPARTMENT DVID DPID DVNAME ex ex Executive ex st Executive Staff hr pr Payroll hr pl Personnel hr mg Management it dv Development it pr Processing it mg Management

You can see that DVID and DPID together are a composite primary key for the DEPARTMENT table, with the DVID being a foreign key to the DIVISION table. Each value of DVID occurs only once in the DIVISION table, but multiple times in the DEPARTMENT table, reflecting the one-to-many relationship. DPID by itself is not a suitable primary key for DEPARTMENT because there are duplicates -- there's a 'mg' department in the 'hr' division, and another 'mg' departmentin the 'it' division.

Let's add a third table.

SECTION DVID DPID SCID SCNAME it dv an Analysis it dv pr Programming it pr de Data Entry it pr op Operations it mg sm Section Managers it mg pl Project Leaders

The SECTION table has a composite primary key consisting of three columns. DVID and DPID together are a composite foreign key to the DEPARTMENT table.

At this point, you can predict what the foreign key in the EMPLOYEE table is going to look like, to relate employees to the sections they belong to -- a composite foreign key consisting of three columns. For some variation, let's use a different primary key for the EMPLOYEE table.

EMPLOYEE EMPNO EMPNAME DVID DPID SCID 024 J.Programmer it dv pr 027 T.Keypunch it pr de 034 P.H.Boss it mg sm

It is important to emphasize that the values of the keys should never be revealed to users of the database. Keys which are codes are often natural keys, and they make excellent primary and foreign keys. In these examples, however, DVID, DPID, and SCID aren't particularly natural, and could just as easily be autonumbers. Just because keys in some application systems are exposed, especially when they are seemingly meaningful codes such as the ones in this example, doesn't make it right to expose keys that aren't natural keys. But this is actually a different debate from the discussion of composite keys, to which I shall now return.

A major advantage of the composite key structure is that because of the transitive nature of relations, you can see that employees are related not just to their sections, but also to their departments and divisions, without the need to declare additional foreign keys -- you could declare them, but you don't need to.

Why is this an advantage? To find all the employees in the Information Technology Division, you do not have to have a four-way join in your query, you can just use --

 select EMPNO, EMPNAME from EMPLOYEE where DVID = 'it'

A disadvantage of composite keys is the ripple effect of changing key values. A Section moved to a new Department would require a change to the DPID value in its foreign key -- but DPID is also part of the SECTION primary key! So, the change would also have to be propagated to all EMPLOYEE rows related to that Section, since the EMPLOYEE composite foreign key also includes DPID. If Departments and Sections are frequently reorganized, composite keys do lose some of their appeal.

Part 2

Dig Deeper on Oracle and SQL