How to Enforce a Constraint Over Multiple Tables

constrainterd

I have the following five tables:

PRODUCT
FEATURE
PROVIDER
PROVIDER_PRODUCT
PROVIDER_FEATURE

The logic is as follows:

A generic PRODUCT can have one or more FEATUREs. There are different PROVIDERs who are delivering the same product, but with different details, and the same FEATUREs, also with different details.

The problem is that with this ERD I can create a PRODUCT p1 with FEATURE f1 and a PRODUCT p2 with FEATURE f2. Then I create PROVIDER pv1. Now I can create a PROVIDER_PRODUCT pvp1 with PROVIDER pv1 and PRODUCT p1. But when I create a PROVIDER_FEATURE pvp1 I should only be allowed to add FEATURE f1, as this is the FEATURE that is linked to p1 via the PROVIDER_PRODUCT table. However I can also add a FEATURE f2.

How can I create a constraint that prevents the user from entering a PROVIDER_FEATURE that is part of a FEATURE that is part of a PRODUCT but that is not the PRODUCT on the PROVIDER_PRODUCT that is on that PROVIDER_FEATURE? Do I need to solve that in a stored procedure, or is there a more elegant way to enforce this?

+-------------------+       +--------------------+
|                   |       |                    |
| PRODUCT           +-----> | FEATURE            |
|                   |       |                    |
+---------+---------+       +----------+---------+
          |                            |          
          |                            |          
          v                            v          

+-------------------+       +--------------------+
|                   |       |                    |
| PROVIDER_PRODUCT  +-----> | PROVIDER_FEATURE   |
|                   |       |                    |
+-------------------+       +--------------------+

          ^                                       
          |                                       
          |                                       
+---------+---------+                             
|                   |                             
| PROVIDER          |                             
|                   |                             
+-------------------+

Best Answer

This can easily be done by using cascading keys that overlap. Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK):

In this example, the PK to Provider Product includes the Product Number provided, and the PK to Feature includes the Feature Number supported for that Product. The Product Number in Provider_Feature is a FK back to both the Provider Product and the Feature. The FK constraints thus prevent inserting a Provider Feature for a Product Feature combination not already instantiated as a Product Feature possibility.

This solution assumes that a feature is identified by a Product - that is a feature cannot exist outside the context of a product. That assumption is likely valid as features, in and of them selves, have no meaning with the context of a product. However, if you do want to instantiate features that can included on multiple products, then you simply add a Feature table and make the current Feature table a Product_Feature table.

Often database designers assume each table must have its own "identifier." Nothing could be further from the truth. Please see Fabian Pascal's excellent blog post on this topic. Adding a surrogate Key to each table means the natural business rule you are trying to enforce can no longer be enforced declaratively via PK and FK constraints, just as you have found. If you must have a SK for some reason your only option is to enforce the constraint procedurally using a trigger. This option is much more problematic. First, you have to write, debug, test and maintain the trigger. The book Applied Mathematics for Database Professionals provides excellent detail on how difficult it can turn out to be to express constraints using procedural logic in a way that performs adequately. Secondly, triggers in and of them selves can sometimes be performance inhibitors as they execute serially.

My recommendation is to use the natural declarative approach.

Related Solutions

How to constrain data

Include the providerid column (and appropriate values) in the UserRating table.
Make (userid, providerid) a unique key for the UserRating table.
Make (id, providerid) a unique key of CreditRating.
Make (ratingid, providerid) a foreign key of UserRating, referencing (id, providerid) in the CreditRating table.

Also, make (providername, typename) a unique key of Provider, if you haven't already. Or better yet, normalize them into separate provider and type tables, with a many-to-many join as appropriate.

Enforce a constraint problem

Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:

CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count < 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage

CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2 ON PHASE_FIRST_STAGE ( UNIQUE_START_STAGE (phase_id, stage_id));

This solution partially solves the problem:

it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
it enforces this same uniqueness in PHASE_FIRST_STAGE
it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue

Best Answer

Related Solutions

How to constrain data

Enforce a constraint problem

Related Question