Synchronized deleting from two tables by expired date

Question

I have three tables in my database:

First one of COMPANY that includes id, name and email.
Second - COUPON that includes id, title, dates, price etc...
Third - join table COMPANY_COUPON that includes id of the companies and id of the coupons which they own.

In java, I have a method that deletes expired coupons using:

DELETE FROM COUPON WHERE END_DATE < CURRENT_DATE

But after deleting expired coupons, I still have their id in COMPANY_ COUPON join table, how can I solve this?

@Override
public void removeExpiredCoupons() throws CouponSystemException {

    String delete = "DELETE FROM COUPON WHERE END_DATE < CURRENT_DATE";

    Connection connection = pool.getConnection();

    try (PreparedStatement pstmt = connection.prepareStatement(delete)) {
        pstmt.executeUpdate();

        // This line can be removed
        System.out.println("All expired coupos are removed.");
    } catch (SQLException e) {
        throw new CouponSystemException("Removing expired coupons is failed. ", e);
    } finally {
        pool.returnConnection(connection);
    }
}

Answer 1

It sounds like you currently have no foreign key constraints defined from COMPANY_COUPON to COUPON (nor to COMPANY). That is undesirable, because it results exactly in the problem you describe.

If you want records in COMPANY_COUPON to get automatically deleted if the record is deleted, you need to define a foreign key constraint from COMPANY_COUPON to COUPON and make it on delete cascade.

For example you can add one with

alter table COMPANY_COUPON 
  add constraint fk_company_coupon_coupon 
    foreign key (COUPON_ID) references COUPON (ID) on delete cascade

You should do something similar to COMPANY.