Search code examples
sqlsql-serversql-server-2014

How to apply a unique constraint across multiple hierarchy levels


I have tables with the following layout:

  • A Project can have many Entity relationships.
  • An Entity can have many DataPoint relationships.

How can I enforce that the Name column of the DataPoint table must be unique per project?

Currently, I have a foreign key in Entity for the project and another foreign key in DataPoint for the entity. I thought about adding the ProjectId as another foreign key column in the DataPoint table so that I could create a unique constraint using DataPoint.ProjectId, DataPoint.Name, but that would introduce a new problem of not being able to guarantee that DataPoint.EntityId is actually a child of DataPoint.ProjectId.


Solution

  • You want to prevent two entities pointing to the same project from having the same data points. This is not directly supported by SQL constraints, which means that triggers are one solution.

    You can effectively do the same thing, if you can incur a bit of extra overhead: include the projectid in datapoint. Then include the following constraints:

     constraint foreign key (projectid, entityid) refers to entities(projectid, entityid);
    
    constraint unique (projectid, name);
    

    The first constraint is redundant (projectid is not needed), but there is little harm with it. The biggest issue is that you need to include the projectid in datapoint -- and remember to insert it.