Violation on Foreign Key

Question

Hi I am a beginner in java db netbeans.,

I have two tables namely transaction and checks tables in transaction table I have columns named [transID], [PayToOrder], [BankCode], [Checknumber]. in checks table I have columns named [checknumber], [dateissued], [amount], [transID]. I am using a form as an entry.

here is the block of code that I used to insert data in the database.

private void btnAddRecordActionPerformed(java.awt.event.ActionEvent evt) {                                             
    // TODO add your handling code here:
    String trID = txtTransID.getText();
    int ID = Integer.parseInt(trID);
    String pto = txtPtO.getText();
    String bc = txtBankCode.getText();
    String cn = txtCheckNum.getText();
    int chNum = Integer.parseInt(cn);
    String amount = txtAmount.getText();
    int amnt = Integer.parseInt(amount);
    String dates = (String) txtDate.getValue();

    try{
        stmt1 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String sql1 = "Select * From transactions";
        String sql2 = "Select * From checks";
        rs1 = stmt1.executeQuery(sql1);
        rs2 = stmt2.executeQuery(sql2);

        rs1.moveToInsertRow();
        rs2.moveToInsertRow();

        rs1.updateInt("transID", ID);
        rs1.updateString("PAYTOORDER", pto);
        rs1.updateString("BANKCODE", bc);
        rs1.updateInt("checknumber", chNum);
        rs2.updateInt("checknumber", chNum);
        rs2.updateInt("AMOUNT", amnt);
        rs2.updateString("DATEISSUED", dates);
        rs2.updateInt("transID", ID);

        rs1.insertRow();
        rs2.insertRow();
        stmt1.close();
        stmt2.close();
        rs1.close();
        rs2.close();

        JOptionPane.showMessageDialog(this, "Successfully Recorded!");
       }

    catch(SQLException err){
        JOptionPane.showMessageDialog(this, err.getMessage());
    }
}

I keep on getting a error:

"Insert on table 'TRANSACTIONS' caused a violation of Foreign Key constraint 'CHECKNUMBER' for key (inputted data).

please if anyone can enlighten me on this part

thanks

Rommel Ando

The primary key is [TRANSID] in TRANSACTIONS tabel the foreign key is the [CHECKNUMBER],. [Checknumber] is the primary key in CHECKS table and the foreign key is the [TRANSID]. — Tyran Xavier Winchester, Jun 19 '13 at 01:31

Java Devil · Accepted Answer · 2013-06-19T02:03:37.613

0

You are trying to insert data into the table TRANSACTIONS with a CHECKNUMBER that does not exist in the table CHECKS.

So if you have the relation you have above, you will not be able to insert this data in this fashion as the transID and Checknumber are the same for both.

You can either remove one of the foreign keys or you could set the checknumber to NULL for the insert of the transaction, insert the checks, then update the transaction check number to what is should be

edited Jun 19 '13 at 02:03

answered Jun 19 '13 at 01:57

Java Devil

10,629
7
33
48

thank you sir I remove one of the foreign key and it worked. the problem now is I can't get an data integrity meaning data is enter in the transactions table with different transid but in the checks table data entry were not entered or allowed. – Tyran Xavier Winchester Jun 19 '13 at 06:22
what happened is the transactions table has two rows of data and the checks table has only one row data coz it was not allowed by the system. – Tyran Xavier Winchester Jun 19 '13 at 06:23

Violation on Foreign Key

1 Answers1