Integrity constraint for tables not immediately related

Question

I am a SQL beginner and I can't figure out how to properly create an integrity constraint for situations like this:

The schema describes a delivery system - each Restaurant offers some items, which can be delivered to customers (outside the visible schema).

The problem comes with the in_delivery table - items from menu are registered with the delivery through this table. With the current state of things, it is possible to add a menu_item to a delivery which is done by a restaurant, but that restaurant may not offer the menu_item!

When inserting into in_delivery, I need to somehow check, if the Menu_Item_MenuItem_ID is present in offers, that has Restaurant_RestaurantID equal to RestaurantID in Delivery associated with the table.

I don't know if I can use a foreign key here, because the tables are not "adjacent"..

What comes into mind is to have a RestaurantID in in_delivery, that would be a foreign key both to Restaurant and Delivery. Then I could find that in offers. Is there a better way?

Thanks for your help

Answer 1

Alternatively you can use a trigger to check the constraint:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Restaurants (
  RestaurantID NUMBER(2) PRIMARY KEY,
  Name         VARCHAR2(30) NOT NULL
)
/

INSERT INTO Restaurants
          SELECT 1, 'Soylent Green Express' FROM DUAL
UNION ALL SELECT 2, 'Helga''s House of Ribs' FROM DUAL
/

CREATE TABLE Menu_Items (
  Menu_Item_ID NUMBER(2) PRIMARY KEY,
  Name         VARCHAR2(20) NOT NULL
)
/

INSERT INTO Menu_Items
          SELECT 1, 'Soylent Green' FROM DUAL
UNION ALL SELECT 2, 'Ribs' FROM DUAL
/

CREATE TABLE Offers (
  RestaurantID NUMBER(2),
  Menu_Item_ID NUMBER(2),
  PRIMARY KEY ( RestaurantID, Menu_Item_ID ),
  FOREIGN KEY ( RestaurantID ) REFERENCES Restaurants ( RestaurantID ),
  FOREIGN KEY ( Menu_Item_ID ) REFERENCES Menu_Items ( Menu_Item_ID )
)
/

INSERT INTO Offers
          SELECT 1, 1 FROM DUAL
UNION ALL SELECT 2, 2 FROM DUAL
/

CREATE TABLE Deliveries (
  RestaurantID NUMBER(2) NOT NULL,
  Delivery_ID  NUMBER(2) PRIMARY KEY,
  FOREIGN KEY ( RestaurantID ) REFERENCES Restaurants ( RestaurantID )
)
/

INSERT INTO Deliveries
          SELECT 1, 1 FROM DUAL
UNION ALL SELECT 2, 2 FROM DUAL
/

CREATE TABLE in_delivery (
  Delivery_ID NUMBER(2),
  Menu_Item_ID NUMBER(2),
  PRIMARY KEY ( Delivery_ID, Menu_Item_ID ),
  FOREIGN KEY ( Delivery_ID ) REFERENCES Deliveries ( Delivery_ID ),
  FOREIGN KEY ( Menu_Item_ID ) REFERENCES Menu_Items ( Menu_Item_ID )
)
/

Just for ease of reading I've created two useful functions (you would probably want some exception handling in them):

CREATE OR REPLACE FUNCTION get_Delivery_RestaurantID (
  p_Delivery_ID Deliveries.Delivery_ID%TYPE
) RETURN Restaurants.RestaurantID%TYPE
AS
  v_RestaurantID Restaurants.RestaurantID%TYPE;
BEGIN
  SELECT RestaurantID
  INTO   v_RestaurantID
  FROM   Deliveries
  WHERE  Delivery_ID = p_Delivery_ID;

  RETURN v_RestaurantID;
END get_Delivery_RestaurantID;
/

CREATE OR REPLACE FUNCTION does_Restaurant_Offer_Item (
  p_RestaurantID Restaurants.RestaurantID%TYPE,
  p_Menu_Item_ID Menu_Items.Menu_Item_ID%TYPE
) RETURN NUMBER
AS
  v_exists NUMBER(1);
BEGIN
  SELECT CASE WHEN EXISTS ( SELECT 1 
                            FROM   Offers
                            WHERE  RestaurantID = p_RestaurantID
                            AND    Menu_Item_ID = p_Menu_Item_ID
                          )
         THEN 1
         ELSE 0
         END
  INTO   v_exists
  FROM   DUAL;

  RETURN v_exists;
END does_Restaurant_Offer_Item;
/

Then just add a trigger to the table to check that the Restaurant offers the item and, if not, raise an exception.

CREATE TRIGGER check_Valid_Delivery_Item
BEFORE INSERT OR UPDATE OF Delivery_ID, Menu_Item_ID
ON in_delivery
FOR EACH ROW
BEGIN
   IF does_restaurant_Offer_Item( get_Delivery_RestaurantID( :new.Delivery_ID ), :new.Menu_Item_ID ) = 0
   THEN
     RAISE_APPLICATION_ERROR (-20100, 'Invalid Delivery Item');
   END IF;
END check_Valid_Delivery_Item;
/

INSERT INTO in_delivery VALUES( 1, 1 )
/

INSERT INTO in_delivery VALUES( 2, 2 )
/

Query 1:

SELECT * FROM in_delivery

Results:

| DELIVERY_ID | MENU_ITEM_ID |
|-------------|--------------|
|           1 |            1 |
|           2 |            2 |

If you try to do:

INSERT INTO in_delivery VALUES( 1, 2 );

Then you get:

ORA-20100: Invalid Delivery Item ORA-06512: at "USER_4_F9593.CHECK_VALID_DELIVERY_ITEM", line 4 ORA-04088: error during execution of trigger 'USER_4_F9593.CHECK_VALID_DELIVERY_ITEM' : INSERT INTO in_delivery VALUES( 1, 2 )

Answer 2

You could enforce your constraint with the following changes:

1. add the restaurant_id column in the in_delivery table
2. add a unique constraint on delivery (delivery_id, restaurant_id) (needed for 3.)
3. change the foreign key from in_delivery -> delivery to point to (delivery_id, restaurant_id)
4. change the foreign key from in_delivery -> menu_item to in_delivery -> offers