cannot set null to a unique column-CodePudding

so this is my table

CREATE TABLE "client" (
"id"    INTEGER,
"name"  TEXT COLLATE NOCASE,
"surname"   TEXT COLLATE NOCASE,
"number"    TEXT UNIQUE COLLATE NOCASE,
"car_brand" TEXT,
"modele"    TEXT,
"phone_nbr" TEXT,
PRIMARY KEY("id" AUTOINCREMENT));

when im adding a new statment from my java application i can add only one time NULL to the column number but i can add many nulls from the db browser this is the code that i use

String number = tf_number.getText();
      if(tf_number.getText().trim().isEmpty())
          number = null;
    String name = tf_name.getText();
    String surname = tf_surname.getText();
    String phoneNbr = tf_phoneNbr.getText();
    String car_brand = tf_brand.getText();
    String modele = tf_modele.getText();
    Client c = new Client(name, surname, number, car_brand, modele, phoneNbr);
    ClientCRUD pcd = new ClientCRUD();
    pcd.addClient(p);

and this is the sql error

[SQLITE_CONSTRAINT_UNIQUE] A UNIQUE constraint failed (UNIQUE constraint failed: client.number)

this is the addClient() fonction

public void addClient(Client t) {
try {
        
    String requete = "INSERT INTO CLIENT(name,surname,number,car_brand,MODELE,phone_nbr)"
      "VALUES ('" t.getClientName() "','" t.getClientSurname() "','" t.getNumber() "',"
              "'" t.getCarBrand() "','" t.getModele() "','" t.getPhone() "')";
    Statement st = MyConnection.getInstance().getCnx()
                .createStatement();

        st.executeUpdate(requete);
        System.out.println("Client added");
        
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    }

any solution ?

CodePudding user response：

A unique constraint means that the same value cannot appear multiple times. So if that table already has a null, then you won't be able to insert that again. It's also possible that the unique constraint prevents null at all ... you'll need to check with your DB.

CodePudding user response：

You code inserts the string "null" and not a real null like your browser do. So there can be only 1 string with value "null" in that unique column.

You can use preparedStatement with parameters instead of the statement you use. E.g:

PreparedStatement preparedStatement = connection.prepareStatement(sql))

This answers how to insert null using prepared statement: Insert null using prepared statement

And your sql string for the query should have the parameters. See more about prepared statements: Prepared Statements Tutorial

CodePudding user response：

 public void save(Person person) {
    String query = "INSERT INTO person_info ("  
            " name_p, "  
            " age, "  
            " address, "  
            " email "  
            ")"  
            "VALUES(?, ?, ?, ?)";

    try(Connection connection = dbConnection.getConnection()) {
        PreparedStatement prepStatement = connection.prepareStatement(query);

        prepStatement.setString(1, person.getName());
        prepStatement.setInt(2, person.getAge());
        prepStatement.setString(3, person.getAddress());
        prepStatement.setString(4, null);

        prepStatement.execute();
    }
    catch (SQLException e){
        e.printStackTrace();
    }
}

##This will solve your problem