0
Hello, I have a database in which I save some information of the establishment in a table and then the information of the same address, in another table. I wonder if there’s any way I can narrow it down as follows. If the address information ( for example ) is not entered for any reason, sql deletes the record it made referring to the establishment.
CREATE TABLE empresa (
idEmpresa serial,
cliente_idCliente INTEGER NOT NULL,
razaoSocial VARCHAR(45) NOT NULL,
nomeFantasia VARCHAR(45) NOT NULL,
cpfCnpj VARCHAR(20) NULL,
ativo INTEGER,
dataAdesao Date,
PRIMARY KEY(idEmpresa),
CONSTRAINT fk_empresa_cliente FOREIGN KEY (cliente_idCliente) REFERENCES cliente (idCliente)
);
CREATE TABLE endereco (
idEndereco serial,
empresa_idEmpresa INTEGER NOT NULL,
nome VARCHAR(45) NOT NULL,
numero INTEGER,
complemento VARCHAR(30) NULL,
cidade VARCHAR(45) NULL,
estado VARCHAR(2) NULL,
PRIMARY KEY(idEndereco),
CONSTRAINT fk_endereco_empresa FOREIGN KEY (empresa_idEmpresa) REFERENCES empresa (idEmpresa)
);
Currently my creation script is like this.
PS: I use the Postgres bank.
//------------------------------------------------------------------------//
//insert establishment
conexaoBD.conexao();
try {
PreparedStatement pst = conexaoBD.con.prepareStatement(
"UPDATE empresa SET razaosocial=?,nomefantasia=?,cpfcnpj=?,dataadesao=? where cliente_idcliente=? and idempresa=?");
pst.setString(1, estabelecimentoBeans.getRazaoSocial());
pst.setString(2, estabelecimentoBeans.getFantasia());
pst.setString(3, estabelecimentoBeans.getCpfCnpj());
pst.setDate(4, new java.sql.Date(estabelecimentoBeans.getDataAdesao().getTime()));
pst.setInt(5, estabelecimentoBeans.getIdCliente());
pst.setInt(6, estabelecimentoBeans.getId());
pst.execute();
JOptionPane.showMessageDialog(null, "Dados do estabelecimento alterados com sucesso!!!");
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "nao foi possivel alterar o estabelecimento !!!");
}
conexaoBD.desconecta();
//enter address
conexaoBD.conexao();
try {
PreparedStatement pst = conexaoBD.con.prepareStatement(
"INSERT INTO endereco (empresa_idempresa,nome,numero,complemento,cidade,estado) VALUES(?,?,?,?,?,?)");
pst.setInt(1, enderecoBeans.getIdEmpresa());
pst.setString(2, enderecoBeans.getEndereco());
pst.setInt(3, enderecoBeans.getNumero());
pst.setString(4, enderecoBeans.getComplemento());
pst.setString(5, enderecoBeans.getCidade());
pst.setString(6, enderecoBeans.getEstado());
pst.execute();
JOptionPane.showMessageDialog(null, "Dados inseridos com sucesso!!!");
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "nao foi possivel salvar o endereco \n" + ex);
}
conexaoBD.desconecta();
Still don’t have the code that inserts the information? The one you posted is just the SQL of the tables.
– Carlos Andrade
@Carlosandrade is here the code I use to enter
– Anderson felipe
Are these procedures done on different occasions? For example: Is the establishment registered today and the address tomorrow? Or are in the same requisition?
– Carlos Andrade
https://sqlbackupandftp.com/blog/postgresql-job-scheduler have you considered a job that checks this missing and delete the parent record ? I would make a notice via email and send it to users to correct the question http://manojadinesh.blogspot.com/2012/08/how-to-send-e-mail-from-postgresql_29.html?m=1
– Motta
@Carlosandrade are made almost simultaneously. If you register the establishment and then the address but for the user was all at once.
– Anderson felipe
Anderson, see if it is possible to perform both Inserts in a single request. In Firebird, I use
execute statement 'insert into tabela 1 (campos) values (valores); insert into tabela 2 (campos) values (valores);';
orexecute block
.– Carlos Andrade
@Carlosandrade I could not do this in postgres, then I am in the case checking if the address was entered because if it was not he will come back and delete the record of the last establishment automatically
– Anderson felipe