I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key.
But, for example, if a hotel records the date and time of check-ins for each customer, and each customer has a customer ID, should I include the customer ID as a primary foreign key or just a foreign key? Because at one time there can be multiple check-ins so date and time is not enough as a primary key?
should I include the customer ID as a primary foreign key or just a foreign key?
It can't be a "primary foreign key" because a PRIMARY KEY is always UNIQUE (and NOT NULL) and presumably a customer can check in again, perhaps at a different day or time.
A PRIMARY FOREIGN KEY is, in my opinion, an anti-pattern in all cases where the table you're referencing is not a compound key. It means there is a strict 0-1:1 relationship, just like all NULLABLE columns. And in all cases, in most databases, it's better to put such relationships on the original table in columns and let them DEFAULT to NULL.
CREATE TABLE foo ( a int, b int );
Is perfectly fine. If b is not there or applicable, you can set it to NULL (or let it default to that).
CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TABLE bar ( a int PRIMARY KEY REFERENCES foo, b int );
Is a bit silly. The only time I can see this making sense is something like this,
CREATE TABLE foo (a int PRIMARY KEY);
CREATE TABLE bar (a int REFERENCES TABLE foo, b int, PRIMARY KEY (a,b) );
In the above case bar has a compound primary key of which a is a constituent that happens to reference table foo.
There is already an accepted answer. This answer may shed new light on the topic.
Look up "shared primary key". This is a design technique that is often used when there is a relationship that is one-to-one, and mandatory in one direction but optional in the other. IS-A type relationships fit this pattern. For example, every car is a vehicle, but not very vehicle is a car. (Some are trucks or motorcycles, etc.).
This pattern is called superclass/subclass in object modeling, and generalization/specialization in ER modeling.
In Shared Primary Key, one column in the table is declared as both the primary key in this table, and a foreign key that references the primary key of some other table. continuing our example, the ID field in the CAR table would be the primary key, and also a foreign key that references the PK of the VEHICLE table.
This allows a each car to have an assigned identity, without introducing yet another identifier, and it allows for rapid joins between VEHICLE and CAR.
You'll get more details when you do the look up.
Note that I've avoided the term "primary foreign key" which is confusing at best.
