sqldatabase

Would the following table containing two foreign keys also be the primary key for that table?

Can a single table which has two foreign keys also be considered the primary key (taken together) of that table? I'm adding a screenshot from the Invitation to Computer Science 6th edition textbook I'm reading that shows the InsurancePolicies table with two foreign keys, but my question is, are these two foreign keys also considered the primary key for that table? The author in textbook does not explicitly state this.

I think the answer is yes but I'm not sure and I couldn't locate a concrete answer so hopefully someone can provide an easy to understand answer.

Figure 14.8

This is the closest answer I could find (here) on SO so my second question would be would by default the two foreign keys be considered the primary key, too?

Solution

  • Yes, a table can have 2 or more foreign keys that together form a composite primary key.

    Composite keys need to be explicitly declared, even when they consist of foreign keys.

    CREATE TABLE InsurancePolicies (
    EmployeeID INTEGER,
    PlanType VARCHAR(50),
    DateIssue DATE,
    PRIMARY KEY (EmployeeID, PlanType),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID),
    FOREIGN KEY (PlanType) REFERENCES InsurancePlans(PlanType)
);

    Without explicit declaration you would not enforce uniqueness and potentially allow duplicate pairs of (EmployeeID, PlanType).