Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My specific example is a set of people who have posted zero or more documents, but only one is their primary. It is not necessarily their latest.

Here's what I'm trying...

Can I enforce a relation both from DOCUMENT to PERSON on personId, and from PERSON to DOCUMENT on a composite key, while allowing null on primaryDocumentId? I'm a little worried this could cause me trouble in my ORM.

1. PERSON                       DOCUMENT
   personId unique        <- -> personId
   primaryDocumentId null    -> documentId unique

Here are some other ideas:

2. PERSON                    DOCUMENT
   personId unique        <- personId
   primaryDocumentId null    documentId unique

This does not prevent inconsistency of a person having someone else's document as their primary. It has the benefit that I can enforce a primary document by removing the null if I wish.

3. PERSON             DOCUMENT with trigger
   personId unique <- documentId unique

This has the problem of the searched/nested action the trigger could require under some circumstances. Also, I've entirely avoided triggers until now and would prefer to keep it that way, especially for core schematic needs.

4. PERSON             DOCUMENT
   personId unique <- documentId unique
                      date unique

This is fun, in that there is inherently only one oldest/newest document for each person. However, that doesn't represent my use in this scenario.

5. PERSON             DOCUMENT                          PRIMARYDOCUMENT
   personId unique <- personId           / composite \  personId unique
                      documentId unique  \ relation  /  documentId

This looks like it might be the right way. I assume it is practiced, but I've never seen it before. Have you seen any risks associated with it?

Or any other suggestions?

  • 1
    See my answer here for a similar problem (in MySQL): In SQL, is it OK for two tables to refer to each other? Approach #2 is exactly what @Rob proposed and works fine in SQL-Server. Approach #4 works in most DBMS but requires an additional table (PRIMARY_DOCUMENT), exactly as your last proposal. Aug 25, 2012 at 9:04
  • I don't know your application, but chances are, you would be interested in more than the document id for a person. You probably want to collect, type, dates, etc. which would call for a separate document table.
    – NoChance
    Aug 25, 2012 at 9:30
  • @Emmad: He already has a DOCUMENT table. Aug 25, 2012 at 9:38
  • ypercube: you are correct, it is the same question at the root. great reference. For education, would you please explain why you say a circular reference (first option on both my and your post) is bad here. The bilateral relation on the unique personId (although via two relations, still concurrent) here ensures there is no recursion. Unless, of course, you could consider a 1-1 relationship recursive. Are you saying it's just a matter of how poorly supported this is across DBMS or ORMs?
    – shannon
    Aug 25, 2012 at 20:52
  • No, I don't mind 1-1 relationships. I don't like circular references in FKs in general, not just in this case. There are problems with inserting and deleting rows from both the 2 tables (or all if there more than 2 tables involved, or less than 2). FKs with cascading delete in not well supported, in some DBMS when there are circular references. I find it cleaner (and possible to do in every case) to remove them by adding one or two tables which represent entities that are not obvious. In this case the "Primary Document for a person". Aug 26, 2012 at 13:36

1 Answer 1


Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;
  • awesome, thanks. just the sort of solution I was looking for.
    – shannon
    Aug 25, 2012 at 17:53
  • it's probably also worth noting that, as ypercube mentioned in the related question he linked above, this does rely on features in my specific DBMS
    – shannon
    Aug 25, 2012 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.