title: Relationships and Multi Column / Composite Unique Keys
body: <p>I have an issue where I need to create a relationship between two tables, but the uniqueness is only between two fields in both of the tables. Neither one of the values is unique on their own.  The database being used is currently in SQL Server 2008 R2.  Here's the actual scenario:</p>
  
  <p>Table B contains a <code>JobNum</code> column, which is already a foreign key to <code>Table A</code>.  <code>Table B</code> also contains a column named <code>Shipment</code>.  This <code>Shipment</code> column is not unique on its own, but when paired with a <code>JobNum</code> the composite is unique. (<code>JobNum</code> AND <code>SHIPMENT</code> = UNIQUE)  I know that I can create a unique index of the composite.  However, since I inherited this project there is already a primary key, which is simply an auto-incrementing identity.  <code>Table C</code> contains both <code>JobNum</code> and <code>Shipment</code> columns, but there is no actual relationship between the two tables.    </p>
  
  <p>How can Table B be linked to Table C using this composite unique value?  Does this need to be done with a join table?</p>