Is it possible to insert row into the child table of FOREIGN KEY?

Refresh

April 2019

Views

40 time

1

I have three tables in my database:

COMPANY(ID, NAME)  
COUPON(ID, TITLE)  
COMPANY_COUPON(COMPANY_ID, COUPON_ID)

Company can create coupons, so when it creates coupon, coupon's ID and ID of the company will be added into COMPANY_COUPON table. When company deletes coupon it also will be deleted from COUPON and COMPANY_COUPON due to FOREIGN KEY. When I delete company it also deleted from COMPANY and COMPANY_COUPON due to FOREIGN KEY, but all its coupons stay in COUPON table, I tried to add into COUPON table FOREIGN KEY(ID) REFERENCES COMPANY_COUPON(COUPON_ID), but because of it I can't insert new coupons into COUPON table.

Will be happy for answers how to create right tables and how to add something that will help to existing tables. Maybe some joins can help with it, I'm really not strong in sql command yet, sorry...

Also, i was using this method to join COMPANY_ID and COUPON_ID in COMPANY_COUPON table, maybe there is some better way to do it.

@Override
public void linkCompanyCoupon(long companyID, long couponID) throws CouponSystemException {

    Connection connection = pool.getConnection();

    String CREATE_COUPON = "INSERT INTO COMPANY_COUPON (COMPANY_ID, COUPON_ID) VALUES (?,?)";

    try (PreparedStatement pstmt = connection.prepareStatement(CREATE_COUPON)) {
        pstmt.setLong(1, companyID);
        pstmt.setLong(2, couponID);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        throw new CouponSystemException("Linking company to the coupon is failed. ", e);
    } finally {
        pool.returnConnection(connection);
    }
}

This one is creates coupon, and inside it i use linkCompanyCoupon();

public void createCoupon(Coupon coupon) throws CouponSystemException {

    // Checking if name of the new coupon is not duplicate
    Optional<Coupon> isCouponTitleDuplicate = Optional.ofNullable(couponDBDAO.getCouponByTitle(coupon.getTitle()));

    if (!isCouponTitleDuplicate.isPresent()) {

        couponDBDAO.createCoupon(coupon);

        companyDBDAO.linkCompanyCoupon(loggedCompany, couponDBDAO.getCouponByTitle(coupon.getTitle()).getId());

    } else {
        throw new CouponSystemException("Coupon title: " + coupon.getTitle()
                + " already exists in database, titles and IDs of the coupons must be unique.");
    }
}

2 answers

1

What you've set up is a many-to-many relationship: a company can have many coupons but a coupon can also belong to many companies. That's why the cascading delete removes the entries in the join table (COMPANY_COUPON) when you delete a COMPANY, but it does not delete any rows in COUPON because those rows may also be related to some other company. If a coupon can only belong to one company then you want a one-to-many relationship: put a company_id foreign key column in COUPON and get rid of the join table.

3

Your database design is not properly normalized for your use case. Hou have a 1-to-N relationship where each company can have multiple coupons and each coupon belongs to a single company. You don't need table COMPANY_COUPON to represent that relationshiip. The correct way to proceed is to store a reference to COMPANY.ID in table COUPON.

ALTER TABLE coupon 
    ADD company_id INT NOT NULL DEFAULT 0;
ALTER TABLE coupon
    ADD CONSTRAINT fk_company_id FOREIGN KEY (company_id) 
    REFERENCES company(id)
    ON DELETE CASCADE;
GMB