sql - Insert into Java DB Compound Key with Prepared Statement Gives Error -

sql - Insert into Java DB Compound Key with Prepared Statement Gives Error -

i have 2 tables in java db, alumnus , certificate many-to-many relationship between them. introduced 3rd table , alumnus_has_certificate maintain one-to-many relationship between 2 tables , new 3rd table. 3rd has compound key made of primary keys of original tables. problem unable insert info 1 duplicate key 3rd table using prepared statement. note : 3rd table has compound key.

below sql code tables.

create table alumnus ( alumnus_id int not null primary key generated identity (start 1 , increment 1), alumnus_no int unique, first_name varchar (45) , last_name varchar (45) , other_name varchar (100), residential_address varchar (300), mailing_address varchar (300), email varchar (120) , telephone varchar (25), employeed boolean not null default false, job_title varchar (200), employer_name varchar(120), employer_address varchar (300) ); create table certificate ( cert_id int not null primary key generated identity (start 1 , increment 1), certificate varchar (300) unique not null ); create table alumnus_has_cert ( alumnus_ck int not null, cert_ck int not null, year_graduated smallint ); --constraints alter table alumnus_has_cert add together constraint alumnus_has_cert_compound_key primary key (alumnus_ck,cert_ck); alter table app.alumnus_has_cert add together foreign key (alumnus_ck) references app.alumnus(alumnus_id) on delete cascade; alter table app.alumnus_has_cert add together foreign key (cert_ck) references app.certificate(cert_id) on delete cascade;

the sql string

string alumnushascert = "insert app.alumnus_has_cert " + "(alumnus_ck , cert_ck ,year_graduated ) " + "values ( ? , ? , ?)";

code snippets of prepared statement

seek (preparedstatement stmalumnuscert = con.preparestatement(certupdate) { stmalumnuscert.setint(1, alumnusid); stmalumnuscert.setint(2, certid); stmalumnuscert.setint(3,year_graduated ); stmalumnuscert.executeupdate(); }

the error message netbeans ide presented below :

integrityconstraintviolationexception: statement aborted because have caused duplicate key value in unique or primary key constraint or unique index identified 'alumnus_has_cert_compound_key' defined on 'alumnus_has_cert'. @ org.apache.derby.impl.jdbc.sqlexceptionfactory40.getsqlexception(unknown source) @ org.apache.derby.impl.jdbc.sqlexceptionfactory40.getsqlexception(unknown source) @ org.apache.derby.impl.jdbc.util.generatecssqlexception(unknown source) @ org.apache.derby.impl.jdbc.transactionresourceimpl.wrapinsqlexception(unknown source) @ org.apache.derby.impl.jdbc.transactionresourceimpl.handleexception(unknown source) @ org.apache.derby.impl.jdbc.embedconnection.handleexception(unknown source) @ org.apache.derby.impl.jdbc.connectionchild.handleexception(unknown source) @ org.apache.derby.impl.jdbc.embedstatement.executestatement(unknown source) @ org.apache.derby.impl.jdbc.embedpreparedstatement.executestatement(unknown source) @ org.apache.derby.impl.jdbc.embedpreparedstatement.executelargeupdate(unknown source) @ org.apache.derby.impl.jdbc.embedpreparedstatement.executeupdate(unknown source) @ wa_poly.updatedata.update(updatedata.java:192) @ wa_poly.myjframe.update(myjframe.java:1067) @ wa_poly.myjframe.updatebuttonactionperformed(myjframe.java:762) @ wa_poly.myjframe.access$1300(myjframe.java:65) @ wa_poly.myjframe$14.actionperformed(myjframe.java:528) @ javax.swing.abstractbutton.fireactionperformed(abstractbutton.java:2022) @ javax.swing.abstractbutton$handler.actionperformed(abstractbutton.java:2346) @ javax.swing.defaultbuttonmodel.fireactionperformed(defaultbuttonmodel.java:402) @ javax.swing.defaultbuttonmodel.setpressed(defaultbuttonmodel.java:259) @ javax.swing.plaf.basic.basicbuttonlistener.mousereleased(basicbuttonlistener.java:252) @ java.awt.component.processmouseevent(component.java:6527) @ javax.swing.jcomponent.processmouseevent(jcomponent.java:3321) @ java.awt.component.processevent(component.java:6292) @ java.awt.container.processevent(container.java:2234) @ java.awt.component.dispatcheventimpl(component.java:4883) @ java.awt.container.dispatcheventimpl(container.java:2292) @ java.awt.component.dispatchevent(component.java:4705) @ java.awt.lightweightdispatcher.retargetmouseevent(container.java:4898) @ java.awt.lightweightdispatcher.processmouseevent(container.java:4533) @ java.awt.lightweightdispatcher.dispatchevent(container.java:4462) @ java.awt.container.dispatcheventimpl(container.java:2278) @ java.awt.window.dispatcheventimpl(window.java:2739) @ java.awt.component.dispatchevent(component.java:4705) @ java.awt.eventqueue.dispatcheventimpl(eventqueue.java:746) @ java.awt.eventqueue.access$400(eventqueue.java:97) @ java.awt.eventqueue$3.run(eventqueue.java:697) @ java.awt.eventqueue$3.run(eventqueue.java:691) @ java.security.accesscontroller.doprivileged(native method) @ java.security.protectiondomain$1.dointersectionprivilege(protectiondomain.java:75) @ java.security.protectiondomain$1.dointersectionprivilege(protectiondomain.java:86) @ java.awt.eventqueue$4.run(eventqueue.java:719) @ java.awt.eventqueue$4.run(eventqueue.java:717) @ java.security.accesscontroller.doprivileged(native method) @ java.security.protectiondomain$1.dointersectionprivilege(protectiondomain.java:75) @ java.awt.eventqueue.dispatchevent(eventqueue.java:716) @ java.awt.eventdispatchthread.pumponeeventforfilters(eventdispatchthread.java:201) @ java.awt.eventdispatchthread.pumpeventsforfilter(eventdispatchthread.java:116) @ java.awt.eventdispatchthread.pumpeventsforhierarchy(eventdispatchthread.java:105) @ java.awt.eventdispatchthread.pumpevents(eventdispatchthread.java:101) @ java.awt.eventdispatchthread.pumpevents(eventdispatchthread.java:93) @ java.awt.eventdispatchthread.run(eventdispatchthread.java:82) suppressed: java.sql.sqlexception: cannot close connection while transaction still active. @ org.apache.derby.impl.jdbc.sqlexceptionfactory40.getsqlexception(unknown source) @ org.apache.derby.impl.jdbc.util.newembedsqlexception(unknown source) @ org.apache.derby.impl.jdbc.util.newembedsqlexception(unknown source) @ org.apache.derby.impl.jdbc.util.generatecssqlexception(unknown source) @ org.apache.derby.impl.jdbc.embedconnection.newsqlexception(unknown source) @ org.apache.derby.impl.jdbc.embedconnection.checkfortransactioninprogress(unknown source) @ org.apache.derby.impl.jdbc.embedconnection.close(unknown source) @ wa_poly.updatedata.update(updatedata.java:212) ... 40 more caused by: java.sql.sqlexception: cannot close connection while transaction still active. @ org.apache.derby.impl.jdbc.sqlexceptionfactory.getsqlexception(unknown source) @ org.apache.derby.impl.jdbc.sqlexceptionfactory40.wrapargsfortransportacrossdrda(unknown source) ... 48 more caused by: java.sql.sqlexception: statement aborted because have caused duplicate key value in unique or primary key constraint or unique index identified 'alumnus_has_cert_compound_key' defined on 'alumnus_has_cert'. @ org.apache.derby.impl.jdbc.sqlexceptionfactory.getsqlexception(unknown source) @ org.apache.derby.impl.jdbc.sqlexceptionfactory40.wrapargsfortransportacrossdrda(unknown source) ... 51 more caused by: error 23505: statement aborted because have caused duplicate key value in unique or primary key constraint or unique index identified 'alumnus_has_cert_compound_key' defined on 'alumnus_has_cert'. @ org.apache.derby.iapi.error.standardexception.newexception(unknown source) @ org.apache.derby.impl.sql.execute.indexchanger.insertandcheckdups(unknown source) @ org.apache.derby.impl.sql.execute.indexchanger.doinsert(unknown source) @ org.apache.derby.impl.sql.execute.indexchanger.insert(unknown source) @ org.apache.derby.impl.sql.execute.indexsetchanger.insert(unknown source) @ org.apache.derby.impl.sql.execute.rowchangerimpl.insertrow(unknown source) @ org.apache.derby.impl.sql.execute.insertresultset.normalinsertcore(unknown source) @ org.apache.derby.impl.sql.execute.insertresultset.open(unknown source) @ org.apache.derby.impl.sql.genericpreparedstatement.executestmt(unknown source) @ org.apache.derby.impl.sql.genericpreparedstatement.execute(unknown source) ... 45 more

the order of parameters in prepared statement should same order in sql statement:

string certupdate = "update app.alumnus_has_cert " + "set year_graduated = ? " //1 + "where alumnus_ck = ? , cert_ck = ? "; //2 , 3 seek (preparedstatement stmalumnuscert = con.preparestatement(certupdate) { stmalumnuscert.setint(1,year_graduated); stmalumnuscert.setint(2, certid); stmalumnuscert.setint(3, alumnusid); stmalumnuscert.executeupdate(); }

the wrong order creating duplicate composite key in table.

ps: question has been updated, reply doesn't apply.

java sql prepared-statement javadb