A not null constraints failed

Question

org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_NOTNULL] A NOT NULL constraint failed (NOT NULL constraint failed: stock.id)

I have three columns in my SQLite DB which are id, pname, mrp but I just want to insert data into two columns, then I did this

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) { 
    try {
        Class.forName("org.sqlite.JDBC");
        Connection con = DriverManager.getConnection("jdbc:sqlite:qmb.db");
        String query="INSERT INTO stock(pname,mrp) VALUES('"+jTextField2.getText()+"','"+jTextField5.getText()+"');";
        executeSQlQuery(query, "Inserted");   // TODO add your handling code here:
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(UpdateStock.class.getName()).log(Level.SEVERE, null, ex);
    }
}

I just want to be able to insert rows by clicking on a button into the database

Read about not null constraint in sql tables. That will give you idea about the issue. — Santosh Balaji Selvaraj, Sep 05 '19 at 08:13
Also read up on how to bind values to parameters in a sql statement instead of trying to build a statement with user-supplied strings directly in it. Don't open yourself up to sql injection. — Shawn, Sep 05 '19 at 08:22
It looks like there is a constraint on the "id" column of your table stating that no entry can have a "null" value in this column. My guess is your table is not configured to automatically generate an "id" for new entries. To fix that either you supply an id when you enter a new entry (not recomended), or you make your database generate it for you. — Patrick, Sep 05 '19 at 08:24
`id` is most likely a PRIMARY KEY but without an auto increment flag. PK are by definition not nullable. You either change your DDL to put this column with a auto sequence or provide the value. PS, please check how to prevent [SQL Injection](https://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection) in java! Concatenation of `JTextField` value is really bad — AxelH, Sep 05 '19 at 08:45
thank you, I think I need to use a preparedStatement then the data will go directly into the database. — Daniel Ikokoh, Sep 05 '19 at 10:10
@DanielIkokoh not using `preparedStatement` has nothing to do with your issue here, but it is generally a good idea. — Alvin Thompson, Sep 12 '19 at 15:18

Alvin Thompson · Answer 1 · 2019-09-12T16:45:50.360

When creating a table in SQLite, the column you want to use as the primary key (id in this case) must have been created with exactly the keywords INTEGER PRIMARY KEY in order for its behavior to become SQLite's lightweight version of auto-increment. This is what allows you to add a row without supplying the column's value (SQLite will fill it in for you). INT PRIMARY KEY will not do it, nor will UNSIGNED INTEGER PRIMARY KEY, nor will any other variation. Try recreating the table with INTEGER PRIMARY KEY as the type for id (you can also add the NOT NULL constraint).

You can optionally add the keyword AUTOINCREMENT to the definition of id (that is, use INTEGER PRIMARY KEY AUTOINCREMENT). This will force SQLite to not reuse IDs of previously deleted rows when adding new rows, but this is slower and takes up more memory and disk space.

Here's an example of how to create your table in SQLite so that you don't have to fill in id when you insert a row:

CREATE TABLE stock(id INTEGER PRIMARY KEY, pname TEXT, mrp TEXT);

P.S. - Since this special functionality of INTEGER PRIMARY KEY works by creating an alias from id to ROWID, you should not also use the WITHOUT ROWID keywords when creating the table.

P.P.S. - You are not closing the SQL connections you created in the code above (there are other major issues I'm ignoring, but that one is the most important). You need to do so in a finally clause to ensure the connection is closed even in the event of an exception:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) { 
    Connection con = null;
    try {
        Class.forName("org.sqlite.JDBC");
        con = DriverManager.getConnection("jdbc:sqlite:qmb.db");
        String query="INSERT INTO stock(pname,mrp) VALUES('"+jTextField2.getText()+"','"+jTextField5.getText()+"');";
        executeSQlQuery(query, "Inserted");   // TODO add your handling code here:
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(UpdateStock.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(UpdateStock.class.getName()).log(Level.WARNING, "can't close SQL connection!" , ex);
            }
        }
    }
}

score -1 · Answer 2 · answered Sep 05 '19 at 08:57

While creating a table you must have made id as primary key. So, id cannot be null. First set your id column for auto-increment. This will only work when you use db query to insert record. If you are trying to manually insert the record in db then you must specify value in id column manually.

A not null constraints failed

2 Answers2