Discussions

Expand all | Collapse all

Can a table have more than one foreign key?

  • 1.  Can a table have more than one foreign key?

     
    Posted 05-29-2018 20:39
    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!

    Thanks. 


  • 2.  RE: Can a table have more than one foreign key?

    Posted 05-29-2018 20:53

    Yes this looks like a strait forward many to many relationship

    take a look at the article https://help.quickbase.com/user-assistance/many_to_many_relationship.html this should guide you in the right direction