The difference between a foreign key and a primary foreign key

database-designforeign keyprimary-keyuniqueidentifier

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?

Best Answer

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.

Related Solutions

The difference between Primary key and Super key in DBMS

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.

A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.

As a database developer/designer of 30 years experience, I had never even heard the term Super Key until I saw this question, and looked it up. The concept of Super Key seems more germane to the topic of performance and Physical Schema design as it directly maps to the concept of a unique nonclustered index with additional columns for improved query covering.

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

The difference between Primary key and Super key in DBMS

Composite primary key from multiple tables / multiple foreign keys

Related Question