title: How to enforce DB integrity with non-unique foreign keys?
body: <p>I want to have a database table that keeps data with revision history (like pages on Wikipedia). I thought that a good idea would be to have two columns that identify the row: <code>(name, version)</code>. So a sample table would look like this:</p>
  
  <pre><code>TABLE PERSONS:
      id:      int,
      name:    varchar(30),
      version: int,
      ... // some data assigned to that person.
  </code></pre>
  
  <p>So if users want to update person's data, they don't make an UPDATE -- instead, they create a new PERSONS row with the same <code>name</code> but different <code>version</code> value. Data shown to the user (for given <code>name</code>) is the one with highest <code>version</code>.</p>
  
  <p>I have a second table, say, DOGS, that references persons in PERSONS table:</p>
  
  <pre><code>TABLE DOGS:
      id:         int,
      name:       varchar(30),
      owner_name: varchar(30),
      ...
  </code></pre>
  
  <p>Obviously, <code>owner_name</code> is a reference to <code>PERSONS.name</code>, but I cannot declare it as a Foreign Key (in MS SQL Server), because <code>PERSONS.name</code> is not unique!</p>
  
  <p><strong>Question</strong>: How, then, in MS SQL Server 2008, should I ensure database integrity (i.e., that for each DOG, there exists at least one row in PERSONS such that its PERSON.name == DOG.owner_name)?</p>
  
  <p>I'm looking for the most elegant solution -- I know I could use triggers on PERSONS table, but this is not as declarative and elegant as I want it to be. Any ideas?</p>
  
  <hr>
  
  <p><strong>Additional Information</strong></p>
  
  <p>The design above has the following advantage that if I need to, I can "remember" a person's current <code>id</code> (or <code>(name, version)</code> pair) and I'm sure that data in that row will never be changed. This is important e.g. if I put this person's data as part of a document that is then printed and in 5 years someone might want to print a copy of it exactly unchanged (e.g. with the same data as today), then this will be very easy for them to do.</p>
  
  <p>Maybe you can think of a completely different design that achieves the same purpose and its integrity can be enforced easier (preferably with foreign keys or other constraints)?</p>
  
  <hr>
  
  <p><strong>Edit:</strong> Thanks to Michael Gattuso's answer, I discovered another way this relationship can be described. There are two solutions, which I posted as answers. Please vote which one you like better.</p>