Can a foreign key of a table be a part of the composite primary key of the same table?

Question

Suppose I have two tables: A (with columns: a,b,c,d) and B (with columns: x,y,z). Now, (a,b) together make the primary key for table A and x is the primary key of table B. Is it possible to make b a foreign key of table A that refers x from table B?

Please reply ASAP!

Thanks in advance! :-)

Answer 1

Can a column in a composite primary key also be a foreign key referencing a primary key of another table? Of course it can. The important question is, when is this a good idea?

The most common scenario is probably the intersection or junction table. Customers can have more than one Address (Shipping, Billing, etc) and Addresses can have more than one Customer using them. So the table CUSTOMER_ADDRESSES has a primary key which references both CUSTOMER and ADDRESS primary key (and for bonus points the ADDRESS_TYPE reference data table too).

My examples use Oracle 12c syntax:

create table customer_address 
    ( customer_id number(38,0) not null
      , address_id number(38,0) not null
      , address_type_code varchar2(3) not null
      , constraint customer_address_pk primary key 
            (customer_id, address_id, address_type_code)
      , constraint customer_address_customer_fk foreign key 
            (customer_id) references customer(customer_id)
      , constraint customer_address_address_fk foreign key 
            (address_id) references address(address_id)
      , constraint customer_address_type_fk foreign key 
            (address_type_code) references address_type(address_type_code)
     );

The second scenario occurs when the primary key of the child table is comprises the parent key and an identifier (usually a number) which is only unique within the parent key. For instance, an Order has an Order Header and some Order Lines. The Order is identified by the Order Header ID and its lines are identified by a monotonically incrementing number. The ORDER_LINE table may look like this:

create table order_line 
    ( order_header_id number(38,0) not null
      , order_line_no number(38,0) not null
      , product_id number(38,0) not null
      , qty number(38,0) not null
      , constraint order_line_pk primary key 
            (order_header_id, order_line_no)
      , constraint order_line_header_fk foreign key 
            (order_header_id) references order_header(order_header_id)
      , constraint order_line_product_fk foreign key 
            (product_id) references product(product_id)
     );  

Note that we could model ORDER_LINE as another intersection table, with a primary key of (order_header_id, product_id) and relegate order_line_no to the status of ordinary attribute: it depends on the business rules we must represent.

This second scenario is rarer than you might think: composite primary keys are pretty rare in real life. For instance, I think the model presented in that other answer is weak. The chances are we will need to use Employee as a foreign key for a number of relationships (e.g. Manager, Assignment, Sales). Using a composite key for foreign keys is clumsy (more typing!). Furthermore, when we drill into these models we often find that one of the key columns is a natural key rather than a primary key, and so might be subject to change. Cascading changes to natural key columns in composite foreign keys is a PITN.

Hence it is common practice to use a surrogate (or synthetic) primary key, say using a sequence or identify column, and enforce the natural key with a unique constraint. The latter step is often forgotten but it is crucial to maintaining referential integrity. Given a situation in which we need to store details of Employees from several Companies, including the Companies' Employee Identifier we might have an EMPLOYEE table like this:

create table employee 
    ( employee_id number(38,0) generated always as number
      , company_id number(38,0) not null
      , company_employee_id varchar2(128) not null
      , name varchar2(128) not null
      , constraint employee_pk primary key 
            (employee_id)
      , constraint employee_uk unique 
            (company_id, company_employee_id)
      , constraint employee_company_fk foreign key 
            (company_id) references company(company_id)
     );  

One situation where it is common to find composite primary keys cascaded to dependent tables is in data warehouses and other VLDBs. Here the composite key columns form part of a denormalization strategy to support Partitioning schemes and/or efficient access paths.

Answer 2

Yes, there is no issue with that. A classic example (using MySQL for demonstration purposes) is a database table holding a number of companies and another holding employees which can work for any one of those companies:

create table companies (
  id            int primary key,
  name          varchar(20));

create table employees (
  id            int,
  c_id          varchar(20) references companies(id),
  name          varchar(20),
  primary key   (id, c_id));

insert into companies (id, name) values (1, 'ABC');
insert into companies (id, name) values (2, 'DEF');
insert into companies (id, name) values (3, 'HIJ');

insert into employees (id, c_id, name) values (101, 1, "Allan");
insert into employees (id, c_id, name) values (102, 1, "Bobby");
insert into employees (id, c_id, name) values (101, 2, "Carol");
insert into employees (id, c_id, name) values (101, 3, "David");

Note that the primary key for employees is a composite key made up of the employee ID and company ID. Note also that the company ID is a foreign key constraint on the primary key of companies, the exact situation (functionally) that you asked about.

The query showing who works for what company shows this in action:

select   c.id, c.name, e.id, e.name
from     companies c, employees e
where    c.id = e.c_id
order by c.id, e.id

c.id   c.name   e.id   e.name
----   ------   ----   ------        
   1   ABC       101   Allan
   1   ABC       102   Bobby
   2   DEF       101   Carol
   3   HIJ       101   David