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
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
... constraint ItemsFKCustomers foreign key (customer) references Customers (id)
A picture instead: should I add the dashed line/FK?
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