What is the difference between a primary foreign key and a non-pr.

[Guest]

Some authors always make the foreign key also a primary key;
while other authors make the foreign key a non-primary key.

Question? what is the difference in effect (operation)?

 

[Rick Brandt]

Some authors always make the foreign key also a primary key;
while other authors make the foreign key a non-primary key.

Question? what is the difference in effect (operation)?

It would be "usual" for a Foreign Key to be part of a multi-column Primary
Key, but it would be unusual for it to be the PK by itself.

An FK usually is on the "many" side of a One-To-Many relationship with the
PK of the other table on the "one" side. If the FK was also the PK (by
itself) then that would make sense in a One-To-One relationship, but those
are not that common.

Example:

Table: Orders
PK=OrderNumber

Table: LineItems
PK = OrderNumber, LineNumber
FK = OrderNumber (references Orders.OrderNumber)

If OrderNumber were also the PK in the LineItems table then you would only
be able to have one LineItem on an Order.