Can a table have more than one foreign key?
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.
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.
