CODEWITHSUNDEEP
androidsqliteconstraints
Justin Breitfeller
Justin Breitfeller

Reputation: 13801

Force constraint error in SQLite when using a rowid integer primary key

I have created a table using the following command:

CREATE TABLE Person (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL)

The problem I am having is that I can insert NULL for the id column despite the NOT NULL constraint. From what I understand, this is because the id column is now an alias for rowid.

If I change the data type of id to INT instead of INTEGER, the constraint works as I expect but I lose the rowid alias (and the benefits that come with it).

My question is: Is there a way to throw a constraint error when passing in NULL for a column that is an alias for rowid?

Upvotes: 0

Views: 400

Answers (2)

Justin Breitfeller
Justin Breitfeller

Reputation: 13801

It seems the short answer here is that there is no way to maintain the rowid alias while also preventing null insertions.

Upvotes: 0

Sam
Sam

Reputation: 86948

The problem I am having is that I can insert NULL for the id column despite the NOT NULL constraint.

If you try to insert null for an INTEGER PRIMARY KEY SQLite will silently use the next appropriate id.

SQLite conveniently interprets INTEGER PRIMARY KEY as INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL. So inserting a null id for the first row will create a row with the id of 1, and so on.

Upvotes: 1

Related Questions