title: Using a foreign key as part of a composite primary key
body: <p>I have two tables in SQL Server. The first is all the 1:1 relationships that belong to individual jobs, which has the primary key declared as follows:</p>
  
  <pre><code>CREATE TABLE Jobs(
  JobNumber bigint PRIMARY KEY )
  </code></pre>
  
  <p>The second table is the list of all of the jobs' components and their 1:1 relationships.  </p>
  
  <p>Each component refers to a single job by its job number, which is a foreign key, and multiple components may refer to the same job. Components are numbered within jobs as 1, 2, 3 and so on.  </p>
  
  <p>Is it possible and reasonable to use the column <code>JobNumber</code> (foreign key) within a composite primary key in the 2nd table, so that the primary key would be made up of (<code>JobNumber, ComponentNumber</code>) as follows:</p>
  
  <pre><code>CREATE TABLE Components(
      JobNumber bigint FOREIGN KEY REFERENCES Jobs(JobNumber) NOT NULL,
      ComponentNumber int NOT NULL,
      PRIMARY KEY(JobNumber, ComponentNumber) 
  )
  </code></pre>
  
  <p>The other option is, of course, to use a surrogate primary key, but this would not enforce the uniqueness constraint on the combination of <code>JobNumber</code> and <code>ComponentNumber</code> (two records in the 2nd table could have <code>JobNumber=1</code> and <code>ComponentNumber=1</code>, for example), so I would prefer to use a composite natural primary key.</p>