Violation of foreign key constraint

Question

I faced this problem INSERT on table 'PURCHASEDETAILS' caused a violation of foreign key constraint 'SQL141108214008671' for key (P0003). The statement has been rolled back. I can't seems to find the problem.

  CREATE TABLE STOCKS (
    stockID VARCHAR(5) not null,
    stockName VARCHAR(50),
    stockPrice DOUBLE,

stockDescrp VARCHAR(100),
stockQty INTEGER,
PRIMARY KEY (stockID)
);

INSERT INTO STOCKS VALUES ('S0001', 'Vitamin C For Kids', 12.90, 'Vitamin C for kids under age of 8.', 100);
INSERT INTO STOCKS VALUES ('S0002', 'Syntha-6 Whey Protein', 180.00,'Ultra lean muscle protein for healthy adults over 18 years old.', 100);
INSERT INTO STOCKS VALUES ('S0003', 'Serious Mass', 190.00,'Extra calories that allows anyone to increase mass efficiently.', 100);
INSERT INTO STOCKS VALUES ('S0004', 'Vitamin C Elite', 15.50, 'Vitamin C for everyone.', 100);

CREATE TABLE PURCHASE (
     purchaseID VARCHAR(5) not null,
     purchaseDate DATE,
     memberID VARCHAR(5),
     PRIMARY KEY (purchaseID),
     FOREIGN KEY (memberID) REFERENCES MEMBERS(memberID)
);

INSERT INTO PURCHASE VALUES ('P0001','2014-10-23','S0001');
INSERT INTO PURCHASE VALUES ('P0002','2014-10-23','G0001');

CREATE TABLE PURCHASEDETAILS (
    purchaseDetailID VARCHAR(7) not null,
    purchaseID VARCHAR(5),
    stockID VARCHAR(5),
    OrderQty INTEGER,
    PRIMARY KEY (purchaseDetailID),
    FOREIGN KEY (purchaseID) REFERENCES PURCHASE(purchaseID),
    FOREIGN KEY (stockID) REFERENCES STOCKS(stockID)
);

INSERT INTO PURCHASEDETAILS VALUES ('PD00001', 'P0001', 'S0001', 4);
INSERT INTO PURCHASEDETAILS VALUES ('PD00002', 'P0001', 'S0002', 10);
INSERT INTO PURCHASEDETAILS VALUES ('PD00003', 'P0002', 'S0003', 5);

Here's the DA part for insert record :

 public void insertRecord(Purchase purc){

    String queryStr = "INSERT INTO "+ tableName +" VALUES (?,?,?,?)";
    try{
        stmt = conn.prepareStatement(queryStr);
        stmt.setString(1,purc.getPurchaseDetailsID());
        stmt.setString(2,purc.getPurchaseID());
        stmt.setString(3, purc.getStockID());
        stmt.setInt(4, purc.getQuantity());
        stmt.executeUpdate();
    }
    catch (SQLException ex){
        JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR",JOptionPane.ERROR_MESSAGE);
    }
}

Here's the UI part :

 if(e.getSource() == jbtCreate)
        {
            String stockID = jTF.getText();
            String quantity = jTF2.getText();
            Purchase purc = new Purchase();




            if(stockID.isEmpty() || quantity.isEmpty())
            {
                JOptionPane.showMessageDialog(null,"Fields must not be empty","ERROR",JOptionPane.ERROR_MESSAGE);
            }
            else{


            String pID = PID();
            count = count +1;
            String pDID = PDID(count);

            int q = Integer.parseInt(jTF2.getText());
            purc.setQuantity(q);
            String sID= jTF.getText();
            int quantityC = Integer.parseInt(jTF2.getText());


             String priceStr = purcControl.selectPrice(sID);
             double realPrice = Double.parseDouble(priceStr);
             purc.setPurchaseDetailsID(pDID);
             purc.setPrice(realPrice);
             purc.setPurchaseID(pID);
             purc.setQuantity(quantityC);
             purc.setStockID(sID);
             double price = purc.getPrice();
             tableModel.addRow(new Object[]{pDID,sID,price,quantityC});

            purcControl.addRecord(purc);
            }



        }

Control :

 public void addRecord(Purchase purc){
    purcDA.insertRecord(purc);
}

Domain:

  public Purchase(String purchaseDetailsID,String purchaseID,String stockID,double price,int quantity)
{
    this.purchaseDetailsID = purchaseDetailsID;
    this.purchaseID = purchaseID;
    this.stockID = stockID;
    this.price = price;
    this.quantity = quantity;

    counter ++;
}
public String getPurchaseID(){
    return purchaseID;
}
public void setPurchaseID(String u){
    this.purchaseID = u;
}
public int getCounter(){
    return counter;
}
public String getPurchaseDetailsID(){

    return purchaseDetailsID ;

}
public double getPrice(){
    return price;
}
public String getStockID(){
    return stockID;
}
public int getQuantity(){
    return quantity;
}
public void setPurchaseDetailsID(String r){
    this.purchaseDetailsID = r ;
}
public void setPrice(double p){
    this.price = p;
}
public void setStockID(String s){
    this.stockID = s;
}
public void setQuantity(int q){
    this.quantity = q;
}

I don't know where the problem is. Whenever I enter , this error just came out.

Answer 1

it appears you are trying to insert a record that references 'P0003' in the Purchase table, but that table does not contain a record with that key value. thus you are getting a foreign key constraint.

you first need to insert the missing Purchase record, OR change the value you are using in your input to reference values that are found in the Purchase table.