title: Foreign Key Used in Composite Primary Key
body: <p>Is it possible to use a composite foreign key as a piece of a table's composite primary key?</p>
  <p>For instance, let's say I have two tables:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE DB.dbo.Partners
  (
      CONSTRAINT pk_Partners_Id
      PRIMARY KEY (Name, City, State, Country, PostalCode),
      
      Name                VARCHAR(100)    NOT NULL,
      Address1            VARCHAR(100),   
      Address2            VARCHAR(100),   
      Address3            VARCHAR(100),   
      City                VARCHAR(150)    NOT NULL,   
      State               CHAR(2)         NOT NULL,   
      Country             CHAR(2)         NOT NULL,   
      PostalCode          VARCHAR(16)     NOT NULL,   
      Phone               VARCHAR(20),    
      Fax                 VARCHAR(20),    
      Email               VARCHAR(256)
  )
  </code></pre>
  <p>... and then in a second table, I would like to reference the foreign key in the second table's primary key:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE DB.dbo.PartnerContacts
  (
      CONSTRAINT pk_PartnerContacts_Id
      PRIMARY KEY (fk_PartnerContacts_PartnerId, FirstName, LastName, PhoneNumber, Email),
                          
      CONSTRAINT fk_PartnerContacts_PartnerId
      FOREIGN KEY REFERENCES Partners(Name, City, State, Country, PostalCode),
  
      FirstName           VARCHAR(75)     NOT NULL,   
      MiddleName          VARCHAR(75),    
      LastName            VARCHAR(75)     NOT NULL,   
      PhoneNumber         VARCHAR(20)     NOT NULL,   
      MobileNumber        VARCHAR(20),    
      FaxNumber           VARCHAR(20),    
      Email               VARCHAR(256)    NOT NULL,   
      MailTo              VARCHAR(100),   
      Address1            VARCHAR(100),   
      Address2            VARCHAR(100),   
      Address3            VARCHAR(100),   
      City                VARCHAR(150),   
      State               CHAR(2),    
      Country             CHAR(2),    
      PostalCode          VARCHAR(16)
  )
  </code></pre>
  <p>Is there any way that I can do that?  Yes, it might be easier to just simply use IDENTITY columns in these tables but if I can define an actual relationship without an IDENTITY I would like to do that.</p>
  <p>EDIT:</p>
  <p>I wanted to provide the final, working SQL.  Thanks to everyone who answered!</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE DB.dbo.Partners
  (
      CONSTRAINT pk_Partners_Id
      PRIMARY KEY (Name, City, State, Country, PostalCode),
      
      Id                  INT             NOT NULL   UNIQUE   IDENTITY(1, 1),
      Name                VARCHAR(100)    NOT NULL,
      Address1            VARCHAR(100),   
      Address2            VARCHAR(100),   
      Address3            VARCHAR(100),   
      City                VARCHAR(150)    NOT NULL,   
      State               CHAR(2)         NOT NULL,   
      Country             CHAR(2)         NOT NULL,   
      PostalCode          VARCHAR(16)     NOT NULL,   
      Phone               VARCHAR(20),    
      Fax                 VARCHAR(20),    
      Email               VARCHAR(256)
  )
  
  CREATE TABLE DB.dbo.PartnerContacts
  (
      CONSTRAINT pk_PartnerContacts_Id
      PRIMARY KEY
      (PartnerId, FirstName, LastName, PhoneNumber, Email),
                          
      PartnerId           INT             NOT NULL CONSTRAINT fk_PartnerContacts_PartnerId FOREIGN KEY    REFERENCES Partners(Id),
      FirstName           VARCHAR(75)     NOT NULL,
      MiddleName          VARCHAR(75),    
      LastName            VARCHAR(75)     NOT NULL,   
      PhoneNumber         VARCHAR(20)     NOT NULL,   
      MobileNumber        VARCHAR(20),    
      FaxNumber           VARCHAR(20),    
      Email               VARCHAR(256)    NOT NULL,
      MailTo              VARCHAR(100),   
      Address1            VARCHAR(100),   
      Address2            VARCHAR(100),   
      Address3            VARCHAR(100),   
      City                VARCHAR(150),   
      State               CHAR(2),    
      Country             CHAR(2),    
      PostalCode          VARCHAR(16)
  )
  </code></pre>