cannot set null to a unique column

Question

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 ?

I guess you are using the SQLite database since SQLite now allows null values for the Unique fields. But MySQL does support a null value for the unique field. — Shofiullah Babor, Oct 14 '21 at 20:05
My guess is that your Java code insert the string "null" and not a real null like your browser does. So because it is unique, only one string "null" can exist in that column. — Pam Stums, Oct 14 '21 at 20:16
@ShofiullahBabor im using SQLITE and it allows the nulls value in unique so what the problem that that's why i cant do that ? — Taher ZRIBI, Oct 14 '21 at 20:19

Pam Stums · Answer 1 · 2021-10-14T20:27:39.390

0

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

edited Oct 14 '21 at 20:27

answered Oct 14 '21 at 20:17

Pam Stums

176
8

yes maybe ! any idea how to insert real null ? – Taher ZRIBI Oct 14 '21 at 20:20
See the tutorial I've referred to. – Pam Stums Oct 14 '21 at 20:24
I will check it and tell you the result, thanks! – Taher ZRIBI Oct 14 '21 at 20:27
I've added a reference to another answer on how to insert a null using prepared statement. – Pam Stums Oct 14 '21 at 20:28

score 0 · Accepted Answer · answered Oct 14 '21 at 20:30

0

 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

answered Oct 14 '21 at 20:30

Shofiullah Babor

412
3
10

@ Shofiullah, Thanks for the complete example! – Pam Stums Oct 14 '21 at 20:52
thanks! now it works – Taher ZRIBI Oct 14 '21 at 20:53
`prepStatement.setString(4, null);` - Doesn't this cause non-null values to be lost? – andrewJames Oct 14 '21 at 21:14

cannot set null to a unique column

2 Answers2