Sqlite does not detect unique key on a table despite primary key being present

Question

I am trying to remove a record from sqlite database. I use dbeaver program for all operations. So when the action is performed, following error is shown:

The error says that unique key is not present. Query for table creation was this:

CREATE TABLE betGroups ( 
id INTEGER PRIMARY KEY AUTOINCREMENT, 
betgroupstatus INTEGER NOT NULL,
dateInserted DATETIME DEFAULT CURRENT_TIMESTAMP)

However, If we check the unique keys tab - we will see that a unique key is present:

So basically, primary key IS considered a unique key. Nevertheless an error is still shown.

Is there a good explanation why I am getting this error? I tried adding a UNIQUE keyword for an id field. table was created, but it made no difference.. Is there anything else I can change either in program options or in SQL script in order for the error to go away.

I am not sure if this is my script issue, a driver issue, or a program issue.

Using:

• org.sqlite.JDBC 3.28.0
• dbeaver 6.1.1

Answer 1

It ended up to be a defect in DBeaver. Quote:

https://github.com/dbeaver/dbeaver/issues/6337

This is side effect of the #424 fix. Currently DBeaver doesn't use unique keys with nullable columns. I believe we should use them in SQLite at least.