Insert with integrity restriction

Question

I have two tables:

Table person:

CREATE TABLE T_AM_OME_PESSOA
(
cd_pessoa NUMBER (8) NOT NULL ,
nm_pessoa VARCHAR2 (60) NOT NULL
) ;
ALTER TABLE T_AM_OME_PESSOA ADD CONSTRAINT PK_AM_PESSOA PRIMARY KEY ( cd_pessoa ) ;

and table forum:

CREATE TABLE T_AM_OME_FORUM
(
cd_forum NUMBER (8) NOT NULL ,
ds_forum VARCHAR2 (256) NOT NULL
) ;
ALTER TABLE T_AM_OME_FORUM ADD CONSTRAINT PK_AM_FORUM PRIMARY KEY ( cd_forum ) ;

and relationships

ALTER TABLE T_AM_OME_FORUM ADD CONSTRAINT FK_AM_PESSOA_FORUM FOREIGN KEY ( cd_forum ) REFERENCES T_AM_OME_PESSOA ( cd_pessoa ) ;

and sequence

-- Sequencia tabela T_AM_OME_PESSOA
CREATE SEQUENCE SQ_AM_OME_PESSOA
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
NOCACHE
NOCYCLE;

where in the person implementation will be an abstract class.. currently via bank, to register a forum, I first register any name in the PERSON table and use the value generated from the sequence as the primary key for cd_forum. however, I would like to register forum directly, making record both in the table person and in the table forum simultaneously.

My DAO

public void gravar(Forum f, Connection conexao) throws Exception {

    String sql1 = "insert into T_AM_OME_PESSOA" + "(CD_PESSOA, NM_PESSOA) values (SQ_AM_OME_PESSOA.NEXTVAL,?)";
    PreparedStatement estrutura = conexao.prepareStatement(sql1);
    estrutura.setString(1, f.getNm_pessoa());
    estrutura.execute();
    estrutura.close();

    String sql2 =  "insert into T_AM_OME_FORUM" + "(CD_FORUM, DS_FORUM) values(?,?)";
    PreparedStatement estrutura2 = conexao.prepareStatement(sql2);
    estrutura2.setInt(1, f.getCd_pessoa());
    estrutura2.setString(2, f.getDs_forum());
    estrutura2.execute();
    estrutura2.close();

test class

f.setNm_pessoa(JOptionPane.showInputDialog("Nome do forum "));
        f.setCd_forum(f.getCd_pessoa());
        f.setDs_forum(JOptionPane.showInputDialog("Descricao do forum "));
        ForumBo.grava(f,con);

this giving

java.sql.Sqlintegrityconstraintviolationexception: ORA-02291: integrity restriction (SYSTEM.FK_AM_PESSOA_FORUM) violated - unmarked motherkey

any suggestions for this INSERT simultaneously in PERSON AND FORUM? Obs.: the class Forum extends Person

Answer 1

Mariana,

I think you can try to make recovery from Quence before running the Inserts. Something like this:

// Objeto que manterá o valor das duas PKs
BigDecimal nextVal = BigDecimal.ZERO;

// Consulta sequence para obter próximo valor
String sql0 = "select SQ_AM_OME_PESSOA.NEXTVAL from DUAL";
PreparedStatement estrutura = conexao.prepareStatement(sql0);
ResultSet rs = estrutura.executeQuery();

while (rs.next()) {
   BigDecimal nextVal = rs.getBigDecimal(1);
}
estrutura.close();

// Insere pessoa
String sql1 = "insert into T_AM_OME_PESSOA" + "(CD_PESSOA, NM_PESSOA) values (?,?)";
PreparedStatement estrutura = conexao.prepareStatement(sql1);
estrutura.setBigDecimal(1, nextVal); // Seta o valor obtido na consulta a sequence
estrutura.setString(2, f.getNm_pessoa());
estrutura.execute();
estrutura.close();

// Realiza o commit para garantir que os dados de pessoa foram inseridos
conexao.commit();

// Insere o forum
String sql2 =  "insert into T_AM_OME_FORUM" + "(CD_FORUM, DS_FORUM) values(?,?)";
PreparedStatement estrutura2 = conexao.prepareStatement(sql2);
estrutura2.setInt(1, nextVal);// Seta o valor obtido na consulta a sequence
estrutura2.setString(2, f.getDs_forum());
estrutura2.execute();
estrutura2.close();

Try to compile the above code and, if it works, give us your feedback, ok?

Abs