NOT NULL constraint with existing "defaultValue"

exru

Hi all.
I have next record: QSqlField("safe", bool, tableName: "servers", required: yes, generated: yes, defaultValue: "QVariant(QString, "0")", autoValue: false, readOnly: false) "false"
When i'm tyring to submiAll() i've got an error: "NOT NULL constraint failed: servers.safe Unable to fetch row". It's normally, becouse we have a constraint, but why "defaultValue" not working even my "field" is NULL? How to mark this field as unsafe to avoid checking it for required?

exru

Thnx!
I found a solution myself: model.removeColumn(model.fieldIndex(attribute));
So sadly that the Qt can't working with "defaults", but SQL can.

JonB

@exru
If safe is of type bool, how is QVariant(QString, "0") a suitable value?

exru

Because i'm using SQLITE3 databse, where bool = int in fact.

JonB

@exru
But you're passing a string! Try a bool or an int!

exru

Thnx!
I found a solution myself: model.removeColumn(model.fieldIndex(attribute));
So sadly that the Qt can't working with "defaults", but SQL can.

JonB

@exru

So sadly that the Qt can't working with "defaults", but SQL can.

How do you reach that conclusion, given that you have never passed the right value type for your default to Qt? How can removing a column from the model possibly be a "solution" which addresses this correctly?

exru

@jonb The QSqlField generated automatically. I don't wanna dig depper into the qt & don't wanna know how he does that. I have another reach experience working with other languages where model behave themself as expected. And it's not mine error where i wrote wrong SQL table or query. In fact, if you make a query like this: insert into table (name) values ("123"); // and it's working correctly, despite existing NOT NULL constraint in another field, why Qt can't do the same?

Kent-Dorfman

@exru said in NOT NULL constraint with existing "defaultValue":

insert into table (name) values ("123"); // and it's working correctly, despite existing NOT NULL constraint in another field, why Qt can't do the same?

because for a multi-column table where some columns have default values, the above insertion is ambiguous, and probably a violation of the SQL standard (even if some DBMS cheat and allow it). If you are not supplying values for ALL the columns in your insert then you must name each column that you are not using a default value for.

exru

@kent-dorfman thank you!

some DBMS cheat and allow it...
You a little wrong thinking this is "cheat". I don't know DBMS which not to do that. At least: MySql, PostgreSQL, SQLITE 100% use that mech.

StarterKit

Hi,
I know that the topic is pretty old but I faced exactly the same error and "Solution: remove a column" is definitely not an option for me :)

I have an SQLite database and I have some tables where values shouldn't be NULL but should have a default value. For example a field definition like label TEXT (12) NOT NULL DEFAULT ('') is what I need. It works pretty well when data are inserted by some query.
But then I have a QTableView on a form for user data entry. And when I do model.submitAll() I get exactly the same error "NOT NULL constraint failed: <table>.<field> Unable to fetch row" if one of these NOT NULL fields were not filled in.

Ok, you may say - it is easy, just put default values in record upon record creation. What I dislike with this approach - I'll have 2 places were default values are defined (in code and in SQL DDL) that leads to obvious risk that some day in future they may become different and bring unknown problems... So, the question is - are there any better solution? Or may I fetch default field values for DB table somehow?
The task is to allow user to omit some fields in the table but then have them set to default values (defined for this table in SQL) on submit.

JonB

@StarterKit
Firstly, eliminate QTableView from the issue. That is just UI. Presumably this is a QSqlTableModel issue. Check how to replicate (or avoid) the issue there. You can always arrange to set whatever value necessary in the model from the UI if needs be.

I would assume --- I don't know about SQLite --- that to activate your desired behaviour for NOT NULL DEFAULT ('') you must not specify that column or a value for it in the INSERT statement. Right? Whereas currently the Qt backed is explicitly passing NULL?

I have not used it, but I think this is what enum QSqlField::RequiredStatus is about. Check what QSqlField::requiredStatus() const currently returns for that column. Use QSqlField::setRequiredStatus(QSqlField::RequiredStatus required) to set it to

QSqlField::Optional 0 The fields doesn't have to be specified when inserting records.

Does that then generate the necessary INSERT statement?

StarterKit

@JonB said in NOT NULL constraint with existing "defaultValue":

I would assume --- I don't know about SQLite --- that to activate your desired behaviour for NOT NULL DEFAULT ('') you must not specify that column or a value for it in the INSERT statement. Right?

yes, this assumption is right. There are no problem if I do INSERT omitting this field(s).

@JonB said in NOT NULL constraint with existing "defaultValue":

Whereas currently the Qt backed is explicitly passing NULL?

Here I don't know for sure - I haven't tried to intercept value in between (I just set table name for QSqlTableModel and use it).

@JonB said in NOT NULL constraint with existing "defaultValue":

I have not used it, but I think this is what enum QSqlField::RequiredStatus is about. Check what QSqlField::requiredStatus() const currently returns for that column.

I didn't know about it, I'll try to check this value and see may it be used or not. Thanks for the idea.

StarterKit

Ok, I tried to use QSqlField::RequiredStatus() but got only PySide6.QtSql.QSqlField.RequiredStatus.Unknown from SQLite.
It appears the only way is to have a proper initialization when new record is created.

JonB

@StarterKit
That result is what I exected it to say. Did you try out:

Use QSqlField::setRequiredStatus(QSqlField::RequiredStatus required) to set it to

QSqlField::Optional 0 The fields doesn't have to be specified when inserting records.

Does that then generate the necessary INSERT statement?

?