I am currently trying to compose a database for my reasearch. While exploring the concepts behind some of my tables I stumbled upon one question I could not find an answer to (after a quick web search):
Table 1 specimens has a column called id with values 0001 to 9999, obviously the primary key for this table.
Table 2 sequences has two columns called specimen (foreign key, specimens.id) and gene. Since I am able and willing to take sevaral genes per specimen, I need at least a compound key consisting of specimen+gene, which would be unique.
Given that scenario, is it possible that a foreign key is part of a compound primary key?
PS: I could also create another column with unique values, which is necessary anyway in order to publish the sequences later. And even though I might do that instead, the question above remains unanswered.