Skip to main content
added 884 characters in body
Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.

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.

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.

Source Link
Evan Carroll
  • 64.7k
  • 49
  • 251
  • 496

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.