Can a table have more than one foreign key?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have an app that I am designing (originally in MS Access) that has 4 tables with the following fields (primary keys in bold)

1) Employee: EmployeeID, Employee #, First Name, Last Name
2) CertificationIssuers: CertificationIssuerID, IssuerFullName, IssuerShortName
3) Certifications: CertificationID, IssuerID (foreign key), Certification Name, Description
4) EmployeeCertifications: EmployeeCertID, CertificationID (foreign key from Certifications Table) EmployeeID (foreign key from Employee Table) IssueDate, ExpiryDate, CertificationNumber 

I wanted to know if it's possible to set up the relationships this way such that Table 4 (EmployeeCertifications) can have two foreign keys from other tables that are NOT primary keys within the EmployeeCertifications Table itself?

Would a composite key made up of EmployeeID+Employee CertID make more sense?

In the end I want to be able to run queries/reports that can show me all of the employees that have certification ABC for example or all employees that have certification ABC that are expired, etc. 

Hope this makes sense!

Photo of Zee Ali

Zee Ali

  • 84 Points 75 badge 2x thumb
  • confused

Posted 1 year ago

  • 0
  • 1
Photo of Eric Turrentine

Eric Turrentine

  • 500 Points 500 badge 2x thumb

Yes this looks like a strait forward many to many relationship

take a look at the article this should guide you in the right direction