Adding integer rows to a SQLite database produces ...

# exposed

[Stephcraft]

Adding integer rows to a SQLite database produces an error.

For instance, I add this score column:

val score = integer("score")

Using createMissingTablesAndColumns(...), I get the following error:

[15:46:25 WARN]: Exception in thread "DefaultDispatcher-worker-1" org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLException: Query returns results
[15:46:25 WARN]: SQL: [ALTER TABLE PlayerRecord ADD score INT NOT NULL]
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:99)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:255)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:232)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:198)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:168)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.Transaction.exec$default(Transaction.kt:164)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.SchemaUtils.execStatements(SchemaUtils.kt:364)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:492)
[15:46:25 WARN]: 	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns$default(SchemaUtils.kt:477)

But when I specify a default value it works, however integers are not nullable by default, they default to 0 (by default). This is a bug, right? Also a form of boilerplate code

val score = integer("score").default(0)

[Chantal Loncle]

Hi @Stephcraft SQLite has specific restrictions for its ALTER TABLE ADD column syntax. Restriction #3 states:

If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

So the new column must be added with a default or be nullable. This can be confirmed by running the plain SQL statement as-is with exec() and retrieving the database exception.

This restriction specific to SQLite is mentioned in the KDocs for createMissingTablesAndColumns(). As we work on fleshing out our documentation site, we will be adding a section specific to this SchemaUtils method (and others), which will provide more immediate and clear details about its use in the different supported databases.

[Stephcraft]

I see, I appreciate the detailed answer