Make an INSERT with integrity constraint

Question

Make an INSERT with integrity constraint

Navigation

#1 by (1 votes)

-1

I have two tables:

Person table:

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 forum table:

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;

being in the implementation person will be an abstract class .. currently via bank, to register a forum, I first register any name in the PERSON table and use the generated value of the sequence as the primary key for cd_forum. however, I would like to register forum directly, registering both the person table and the forum table 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);

It's giving

java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (SYSTEM.FK_AM_PESSOA_FORUM) violated - unmanaged parent key

Any suggestion for this simultaneous INSERT in PERSON and FORUM? obs .: class Forum extends Person

java jdbc

asked by anonymous 15.10.2016 / 05:15

1 answer

Error calling a method in ASP.NET Web API and Ninject How to use your renamed [closed]

score 1 · Accepted Answer

Mariana,

I think you can try to recover the sequence 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, okay?

Abs