Adding a constraint on foreign key

Question

I have a superclass Promotion and it has 3 subclasses GiftCard, Offers, TodaysDeals.

Promotion(PromotionID,expiryDate)
GiftCard(GiftCardID,points)
where GiftCard.giftcardID references promotion
TodaysDeals(TodaysDealsID,Discount)
where TodaysDeals.TodaysDealsID references promotion
Offers(OffersID,Discount)
where offers.offersID references promotion

GiftCard promotions are related to customers while Offers & Deals are related to the product. A product can't have an offer and a deal at the same time. I want to add the promotionID into the table of Products, ensuring that any promotionID corresponding to the GiftCardID isn't in the table. Is that approach possible to be represented in the eerd and translated to sql code?

I thought of doing a relation between deals and product and another between offer and product, but that won't satisfy the constraint that a product can't have an offer and a deal at the same time, because both ID's will be represented as foreign keys in the Product Table.

Unfortunately, conditional foreign key definitions are not supported. — Gordon Linoff, Oct 16 '19 at 18:43
This looks like you could combine GiftCard, TodaysDeals and Offers into a single table with a column of PromotionType to indicate which of the three types it is. — Sean Lange, Oct 16 '19 at 19:03
@SeanLange but each of these entities have relations with different other entities, so I don't want to eliminate their table — HR1, Oct 16 '19 at 19:08
You didn't mention that part. But it wouldn't mean those other relationships would have to be eliminated. They would just need to point to the new table. Unfortunately this is really abstract at this point because we have almost no details of the tables at hand and zero knowledge of the project and the rest of the database. With some more details perhaps somebody can help you find a solution. — Sean Lange, Oct 16 '19 at 19:14
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Oct 17 '19 at 00:09

score 0 · Accepted Answer · answered Oct 16 '19 at 19:22

You can add PromoType to your tables, make Promotion(PromotionID,PromoType) UNIQUE and reference this unique key from the other tables.

Promotion(PromotionID, 
         PromoType check ('Deal','Offer', 'Gift'),
         expiryDate,
         UNIQUE(PromotionID,PromoType)
)

Product(
  PromoId,
  PromoType check ('Deal','Offer'),
  FK (PromoId,PromoType) ref Promotion(PromotionID,PromoType)
)

Adding a constraint on foreign key

1 Answers1