Simple example: there is a table of customers.

create table Customers (
  id integer,
  constraint CustomersPK primary key (id)

All the other data in the database should link to a Customer, so e.g. Orders looks like this:

create table Orders (
  id integer,
  customer integer,
  constraint OrdersPK primary key (customer, id),
  constraint OrdersFKCustomers foreign key (customer) references Customers (id)

Suppose now there is a table linking to Orders:

create table Items (
  id integer,
  customer integer,
  order integer,
  constraint ItemsPK primary key (customer, id),
  constraint ItemsFKOrders foreign key (customer, order) references Orders (customer, id)

Should I add a separate foreign key from Items to Customers?

constraint ItemsFKCustomers foreign key (customer) references Customers (id)

A picture instead: should I add the dashed line/FK?

Simple example schema

Edit: I have added primary key definitions to the tables. I'd like to re-iterate on the point I made above: the database is basically siloed by customers, as a correctness / security measure. Therefore, all primary keys contain the customer ID.

  • 2
    No, you shouldn't. There is no need for the extra FK. The constraint is enforced by the other two FKs. Commented Aug 14, 2014 at 9:10
  • @ypercube Are there any performance penalties for having a redundant FK? Any advantages you could think of?...
    – vektor
    Commented Aug 15, 2014 at 6:31
  • 1
    @vektor, the performance aspects probably vary from one rdbms to another, but generally, you get a performance impact for each new FK you add, because every insert/update/delete in one of the PK/FK tables has to be checked against the constraint. With large PK tables, this performance penalty can be quite severe. Commented Aug 15, 2014 at 7:11

3 Answers 3


I think this is the original idea.

enter image description here

First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example.

Second thing to note is the confusion resulting from the use of the generic id name for an attribute.

The CustomerOrderNo should ideally be (1,2,3..) for each customer and OrderItemNo (1,2,3 ...) for each order.

Well, this is nice if possible, but requires a query looking for the previous max value, like

select max(CustomerOrderNo)
from Order 
where CustomerID = specific_customer ; 

which is often not preferred in high-transaction-volume environments, so it is common to see these replaced by an auto-increment, essentially serving the same purpose. It is true that this auto-incremet is now unique, hence it can be used as a KEY -- but you may choose to look at it as a necessary compromise for the OrderItemNo.

So, with some renaming CustomerOrderNo -> OrderNo and OrderItemNo -> ItemNo you may arrive to this model

enter image description here

So now if you look at the Order the following are unique

{OrderNo}             -- PK
{CustomerID, OrderNo} -- superkey,  AK on the diagram.

Note that {CustomerID, OrderNo} is propagated to the LineItem to serve as a FK.

If you squint a bit, this is close to your example, but with PKs {ItemNo} and {OrderNo} only -- as opposed to two column PKs from your example.

Now the question is, why not simplify to something like this?

enter image description here

Which is fine, but introduces PATH DEPENDENCE -- you can not join LineItem with Customer directly, must use Order in the join.

I prefer the first case when possible -- you choose your favourite. And obviously, there is no need for direct FK from LineItem to Customer in these three cases.

  • I searched around, but I can't see "path dependence" being used as a widely adopted term for what I would refer to as "2nd degree transitive relationship" (though my terminology is probably incorrect too)
    – Dai
    Commented Jun 10, 2016 at 9:03

The "item" shouldn't reference the "customer" directly, because this is implied by the item's "order". So, you won't need the "customer" columns on the "items" table at all.

The item's relation to the customer is ensured with the existing foreign key.

If orders.id is an identity column, consider removing items.customer alltogether.

  • 1
    Thanks, I didn't notice that "customer" was also included in the first FK from "items" to "orders". I've elaborated my answer accordingly. Commented Aug 14, 2014 at 11:11
  • @DanielHutmacher I have edited the question to contain primary keys of my tables. This explains the weird FKs you mention in your edit.
    – vektor
    Commented Aug 14, 2014 at 14:22
  • Ok, I've updated my answer. :) Commented Aug 14, 2014 at 14:30
  • I'm guessing that having customer in all tables (and thus siloing the DB) is an unusual approach. I must confess that it's just something I've seen in my previous work. Does it make any sense to you? Have you seen such a design before?
    – vektor
    Commented Aug 14, 2014 at 14:48
  • I'd say it looks like a datawarehouse (star schema) approach, where you want to intentionally denormalize data to eliminate joins. Or the primary key could be composite, i.e. customer A's first, second, third order, customer B's first, second order, etc - when the order id column alone isn't unique. Commented Aug 14, 2014 at 14:53

From a data integrity point of view the transitive foreign key is redundant. And in principle, with a well written database management system, it wouldn't make any difference to performance, since the DBMS could see that the transitive FK is implied by the other two, so would not need to check it separately.

Sadly this is not the case in the real world. I found that with Microsoft SQL Server, the query planner wasn't smart enough to spot that if there is a foreign key from table A to table B, and from B to C, then it can assume a foreign key from A to C when working out cardinality. Adding the transitive FK explicitly, even though redundant, improved performance on some queries.

What I settled on is to add the 'underlying' foreign keys for data integrity, and to decide on a case by case basis whether to add the 'transitive' FKs, depending on query performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.