Monday, February 21, 2011

When does a foreign key not reference a primary key ?

Generally your foreign key will point to the primary key in the parent table. However it is actually possible to point a foreign key to any column, or set of columns, where uniqueness is enforced through a constraint.

One situation where this may be applicable is a type / subtype on your entities. I'm using the familiar employee, department and orders model as it was used in the Stackoverflow question that prompted this. Incidentally, the "Subscriber Content" (ie questions, answers and comments) on StackOverflow are all under licensed as Creative Commons, so there are no legal issues with copyright as might be experienced elsewhere.

The problem was that of ensuring orders could only be owned by certain types of employees, the salesmen. By the way, is anyone else worried that the abbreviation for Sales and Marketing is S&M. Now if you are a data modeling fundamentalist, you would normalize out 'SALESMAN' as a separate entity from 'EMPLOYEE', with an optional one-to-one relationship. I'm not saying that is the wrong solution, but simply offering an alternative which may offer practical benefits in some circumstances (eg reduced storage and de-duplication, and maybe tighter consistency in column statistics).

Firstly, I create the employee table. I add an extra field which is always populated for salesmen, and never for another type of employee. I enforce the value of this to be the same as the employee id, although this is not really essential as long as it is unique. And that uniqueness MUST be enforced by a constraint and not just by an index.

  (emp_id number PRIMARY KEY, emp_name varchar2(20),
  dept_name varchar2(10),
  sales_emp_id number,
  CONSTRAINT sales_emp_id_sales_ck CHECK
      ((sales_emp_id IS NULL AND dept_name != 'SALES') OR
       (dept_name = 'SALES' AND sales_emp_id = emp_id
                            AND sales_emp_id IS NOT NULL)),
  CONSTRAINT sales_emp_id_uk UNIQUE (sales_emp_id));

I insert some employees, with the last three expected to fail due to the constraint violation.

INSERT INTO t_emp VALUES (1,'Alan','SALES',1);
INSERT INTO t_emp VALUES (3,'Chuck','ACCOUNTS',3);
INSERT INTO t_emp VALUES (5,'Ellen','SALES',6);

SELECT * FROM t_emp;

Now I create the ORDERS table. Rather than use the primary key of the EMPLOYEES table, I use the unique salesman's identifier. This ensures that orders can only belong to a salesman. 

  (ord_id number PRIMARY KEY,
  salesman number,
  CONSTRAINT salesman_fk FOREIGN KEY (salesman)
       REFERENCES t_emp(sales_emp_id));

Finally, a simple test to show that only the sales employees can own orders.

INSERT INTO t_orders VALUES (1,1);
INSERT INTO t_orders VALUES (2,2);


Alex Nedoboi said...

Hi Gary,

1. A data modeling fundamentalist would separate salesman from employee? That's fundamentally wrong :)

You can/should have a separate table, salesman_stuff_only, that would have a mandatory 1-2-1 relationship to employee.

Emp (id PK, name)

Salesman_stuff_only (id FK to emp PK, licence, smartphone, management_talk /* helicopter_vision, blue_sky_thinking */ default 1/0)

2. Speaking of (mandatory) FKs. Give me one good reason to have them (adhocs don't count). It's not like your app would go -

insert into order_items (id, order_id, ...)
values (seq.nextval, dbms_random.value(), ...)

Of course you would have an index on order_id if you join on order_items.order_id = all the time. But FK? What for?


Anonymous said...

>>"Finally, a simple test to show that only the sales employees can own orders."

But in your example, there can be orders which don't belong to any salesman (!!) The column t_orders.salesman has been defined as null.



Gary Myers said...


Agreed. Modelling subtypes may or may not result in differentiation of the entities, depending on whether the subtype implies additional attributes.

On foreign keys, Marcel (who came up from Canberra to present to us) raised this issue. Rather than re-open it, I'll refer to the discussion at Richard Foote's (also a Canberra person) blog that followed.

Gary Myers said...


Yup. I should have made salesman not nullable so all the hard work done by sales people does not go without its due reward.