How to enforce inte

Posted on 2016-09-08
Last Modified: 2016-09-10
I'm trying to work out a way to prevent against a data integrity issue we've identified in our SQL Server 2012 DB.  We implement the logic in the application, but I wanted to know if there was a way to lock it down at the DB.

Consider a business which provides our company with licenses we can print on our products.  A product is always associated with one of these businesses.  A product can optionally be associated with one of that businesses' licenses.

For example, consider the Business Marvel who provides us Licenses "Spiderman" and "Iron Man".  We could print a Product (tshirt) with just "Marvel" on it (Business: Marvel, License: Null) or a Product with "Spiderman"  on it (Business: Marvel: License: Spiderman).  

Here's the schema:

The issue is how to ensure that a product associated with one business is not associated with a license for a another business.

Question by:canuckconsulting
  • 5
  • 2
LVL 14

Expert Comment

by:Megan Brooks
ID: 41790207
Let me make sure I understand. You have a parent entity, Business (Business Unit) and an optional child entity, License. You want to be able to associate a third entity, Product, with a Business and optionally with a License.

If License were not optional, you could simply make Product a child of License. You could also define a "No License" License row for each Business that needs it in order to make this work, but then you would need to ensure that only one row had the flag set for each Business ID.

If you want to keep Product as an association between Business and License, however, you can. Assuming that you want each combination of Business ID and License ID to be unique for License, you could define a unique constraint for those columns on License. You could then relate Product to License using both those columns in the FK instead of just License ID.

I feel like there should be a simpler solution, but the relationships here are a little unusual and I haven't had to work this one out before. Does this look like a step in the right direction?

Author Comment

ID: 41790221
Hi rscowden

Yes, I think you have it.  Both a Product and a License must have a parent business.  A Product may optionally have a parent License but, if it does, the License Business parent and Product Business parent should be the same.

This actual sprang from an earlier Expert Exchange Question.  I was hoping there was  a better way to deal with it than expanding the foreign key.  It just feels wrong.  In fact, if it comes down to that being my only choice I'd probably just leave it as something to be handled by code.
LVL 14

Expert Comment

by:Megan Brooks
ID: 41790255
I will have another look at this when I am free.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 14

Accepted Solution

Megan Brooks earned 500 total points
ID: 41790379
It seems like an FK defined against a unique constraint would be the fastest-executing solution, but it does introduce another index and the associated storage.

If insert performance for Product rows is not critical, another possibility might be using an INSTEAD OF trigger. I really don't like triggers, for all the problems I have seen them cause over the years, but an INSTEAD OF is cleaner than an AFTER trigger - no rollback is required. It cancels the original query, and the trigger will have to re-execute it once the key validation has been performed successfully. You would need to intercept both INSERT and UPDATE.
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41791051
You database model doesn't allow a Product to have more than one Business License, i.e., a Product (t-shirt) can have "Marvel: Spiderman" and "Marvel: Ironman". Is really that the intention? If it is then table Product is missing the BussinessID (NOT NULL).
Btw, your License table is missing the LicenseID. It only has BussinessID.
LVL 14

Expert Comment

by:Megan Brooks
ID: 41791553
Some IDs are missing from the schema. They are all shown in the diagram.

Author Closing Comment

ID: 41792583
Thanks Megan.  I think your FK suggestion really is the ticket.  

I've gotten into the habit of always having an Identity PK and using that alone as my FK.  I love the cleanness and flexibility of it (ANYTHING can change and the FK stays valid) but I'm letting my fussiness get in the way of what is clearly a better approach.
LVL 14

Expert Comment

by:Megan Brooks
ID: 41792703
I gave quite a bit of thought to how to do this, because it was a type of relationship I hadn't encountered before. You need to relate Product to both Business and License, while factoring in the relationship between those two tables as well, and you can't create an FK that references two tables, so you need to have a unique constraint on License ID that also relates it to Business ID at the same time. You can't add the Business ID to the License PK, because that would allow duplication of the License ID.

The extra unique constraint feels redundant, since there is already a PK-FK relationship between Business and License, but it isn't used here to enforce uniqueness. Instead, it serves to 'relay' the Business key to the Product-License relationship, so that both the Business and License PKs are able to participate in the Product FK relationship. The result is a single, fast, FK B-tree lookup that serves to protect both Product-License and Product-Business.

FKs are designed both for referential integrity and for speed, so while conceivably the same thing could be accomplished in a less efficient manner, using a secondary lookup to save storage, there is no option I can see to make the FK less efficient and you would be stuck with using triggers, and it would take two of them to have the equivalent of the unique constraint.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 53
push and Pull replication 31 46
SQL server client app 3 26
Trouble installing msi file with msiexe.exe 2 15
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question