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.


CREATE TABLE t_emp
  (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 (2,'Bill','ACCOUNTS',NULL);
INSERT INTO t_emp VALUES (3,'Chuck','ACCOUNTS',3);
INSERT INTO t_emp VALUES (4,'Dan','SALES',NULL);
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. 


CREATE TABLE t_orders
  (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);