NOT NULL Constraint not working on the PRIMARY KEY column in SQLite android

Question

I'm trying to build a simple application where the user isn't allowed to insert duplicate values.

I'm trying PRIMARY KEY as one of the constraints and NOT NULL also. But NOT NULL doesn't seem to work.

I tried my best but failed to resolve it. Please help

((ID INTEGER NOT NULL PRIMARY KEY)";) 

Porush

please show more infos, for example complete DDL + how you insert into the table. – Sturm

Answer 1

SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.

For example:

CREATE TABLE suppliers(
    supplier_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Because this column is declared as INTEGER PRIMARY KEY, it will not accept NULL values.

Body

Answer 2

Try this format:

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Krigsman

How would changing INTEGER to INT make a difference? – Gathers

YES, INT will not work as auto increment. If you want to make that column to be auto incremented. Then simply replace INT with INTEGER. It should work fine. – Krigsman

Answer 3

In SQLite, you cannot add NULL to INTEGER PRIMARY KEY and INTEGER PRIMARY KEY AUTOINCREMENT columns whether or not they have NOT NULL constraint.

For example, you create test table with id column which has INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT with or without NOT NULL as shown below:

CREATE TABLE test (
  id INTEGER PRIMARY KEY NOT NULL
);

Or

CREATE TABLE test (
  id INTEGER PRIMARY KEY
);

Or:

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

Or:

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

Then, you insert 2 rows with NULL for id column as shown below:

INSERT INTO test (id) VALUES (NULL);
INSERT INTO test VALUES (NULL);

Now, the numbers 1 and 2 are added instead of NULL as shown below. *My answer and my answer explain how to show column names and NULL values respectively:

sqlite> .headers on
sqlite> .mode box
sqlite> .nullvalue NULL
sqlite> SELECT * FROM test;
┌────┐
│ id │
├────┤
│ 1  │
│ 2  │
└────┘

In addition, you create test table with id column which has TEXT PRIMARY KEY with NOT NULL as shown below:

CREATE TABLE test (
  id TEXT PRIMARY KEY NOT NULL
);

Then, you try to insert 2 rows with NULL for id column as shown below:

INSERT INTO test (id) VALUES (NULL);
INSERT INTO test VALUES (NULL);

Now, you cannot insert them getting the error below:

Runtime error: NOT NULL constraint failed: test.id (19)

Next, you create test table with id column which has TEXT PRIMARY KEY without NOT NULL as shown below:

CREATE TABLE test (
  id TEXT PRIMARY KEY
);

Then, you insert 2 rows with NULL for id column as shown below:

INSERT INTO test (id) VALUES (NULL);
INSERT INTO test VALUES (NULL);

Now, NULL is added as shown below:

sqlite> .headers on
sqlite> .mode box
sqlite> .nullvalue NULL
sqlite> SELECT * FROM test;
┌──────┐
│  id  │
├──────┤
│ NULL │
│ NULL │
└──────┘

Hypercriticism

Answer 4

Try to use an UNIQUE constraint on the corresponding fields.

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.

---

Edit:
According to your additional info you can use a CHECK constraint to prevent blank values.

sqlite> create table foo (bar TEXT, CHECK(bar <> ''));

See this answer for more information: Not empty string constraint in SQLite

Patently

that's right. but if the primary key is an auto increment for example, it doesn't help you to ensure that there are no duplicate entries. – Patently

An autoincrementing key allows neither duplicates nor NULLs. – Gathers

that's also right. i know what's a primary key is good for and i also know how to use an unique constraint. i just try to help to answer the question and the problem with the duplicate data. it seems that the primary key doesn't solves this issue and therefore i suggest to use an unique constraint on the corresponding fields to solve this issue. – Patently

I tried with the UNIQUE constraint also but, still its not working. I have used PRIMARY KEY on NAME field as I am having just a single column in my db. So I am trying that, the user should be able to INSERT the particular NAME only once and I also want that the user should not be able to be able to INSERT blank as a NAME. PRIMARY on NAME is working but the user is able to INSERT a blank space in db. The NOT NULL Constraint is not working. Any help in this regard is appreciated. – Porush

@user4569636 That information belongs in the question. – Gathers