Restrict way to insert into SQL tables

Asked

Viewed 47 times

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.

  • @Carlosandrade is here the code I use to enter

  • Are these procedures done on different occasions? For example: Is the establishment registered today and the address tomorrow? Or are in the same requisition?

  • 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

  • @Carlosandrade are made almost simultaneously. If you register the establishment and then the address but for the user was all at once.

  • 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);'; or execute block.

  • @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

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.