QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord

[JonB]

@Stroopwafe1 said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

I can only conclude from this that Qt, if it finds an INT PK, that it automatically increments it, even though on the DB side it's not AUTOINCREMENT.
I want to be wrong on this

I really don't think so. And if it did I believe you would see autoValue: true for that column. (Possibly even readOnly: true as well for this case, don't know.) If you want you could compare against what you see if you actually make the db column AUTOINCREMENT.

If I were to have a play with this I would really want (a) a complete program, nothing I need to add to myself plus (b) simplified a lot, you have quite a bit of stuff I would have to write at present. Anything more than 100 lines (and preferably less) is too much. When I submit a problem I pride myself on reducing it so that every remaining line is essential to the issue! Up to you.

[Stroopwafe1]

I definitely can work on a minimum compilable example

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

Anything more than 100 lines (and preferably less) is too much. When I submit a problem I pride myself on reducing it so that every remaining line is essential to the issue! Up to you.

I will do my best to keep it as small, yet still relevant as possible. I will leave out all the other tables as well since those are not part of the problem (only debugging whether composite int PK breaks things)

However, I'm not well-versed in how to share these mini projects on forums. Do you want it as code blocks in a reply? Or as a git repo? Or does QT Creator have some fancy feature to share a project?

[JonB]

For my part, I would usually only look at/copy 100 lines which are pasted here (line at top and at bottom must read ``` (3-backticks) to make it code block). However having got this far I would accept a simple github repo from you if it's easy, whatever gives me less work to do to get it compiling and running. What I would ask is for you to try to remove absolutely anything/every line which is not required to illustrate the issue. I do not care about your real-world requirements, I care only about whatever is minimal to show a problem. Please do include sending the CREATE TABLE string to the db as I am not familiar with SQLite and don't have any tool which might be required to create a database/table outside of your code.

Looking back at your examples, I think versions 3 or 4 are the most useful. (Actually looks like 4 is the best, 3 allows a PK to be NULL.) I agree I see just the food column alone seeming to change from 3 to 0 which looks odd.

P.S. IMPORTANT
What is this I see about SQLite when I Google does sqlite make first column be autoincrement:

SQLite

https://www.sqlite.org › faq

26 Nov 2024 — Short answer: A column declared INTEGER PRIMARY KEY will autoincrement. Longer answer: If you declare a column of a table to be INTEGER ...

?!

I think you need to read https://www.sqlite.org/lang_createtable.html#rowid. You may need to look into

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

The rowid (and "oid" and "rowid") is omitted in WITHOUT ROWID tables. WITHOUT ROWID tables are only available in SQLite version 3.8.2 (2013-12-06) and later. A table that lacks the WITHOUT ROWID clause is called a "rowid table".

If I read right, SQLite makes assumptions about you using an INT as a PK which you do not want in your case....

I think it's saying you need to add WITHOUT ROWID if you want to manage the PK INT value yourself?

[Stroopwafe1]

@JonB

Oh, so it's SQLite that's being dumb in this case? Well that's what I get for assuming all DB managers are basically the same.
I will try to look into the RowID, and if that doesn't solve it, I will still get back to this post then with the minimal example that I was working on right now.

Thank you for your time already though, it might not be solved yet, but I do appreciate you taking a look

[JonB]

@Stroopwafe1
I just added the final sentence:

I think it's saying you need to add WITHOUT ROWID if you want to manage the PK INT value yourself?

Not bad from me, given that I don't use SQLite :) I am familiar with MySQL/MariaDB and MS's SQL Server/T-SQL. They don't have this behaviour!

[JonB]

@Stroopwafe1
P.S.
...Or if you prefer you might allow the SQLite table to have an autoincrement single-field INT PK for its "rowid" (maybe being SQLite it works better like that since it seems to want it?) and make your composite PRIMARY KEY("food","date") be something like a "UNIQUE [INDEX]" instead....

[Stroopwafe1]

@JonB
Modifying the table to be WITHOUT ROWID I get a non-null constraint error again with version 4.
HOWEVER, doing it in my small mini example, it seems that it doesn't do the generation: no and value to NULL. So I have something wrong in my main project. I guess the mini example is also just a good way to test where the problem actually is...

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

...Or if you prefer you might allow the SQLite table to have an autoincrement single-field INT PK for its "rowid" (maybe being SQLite it works better like that since it seems to want it?) and make your composite PRIMARY KEY("food","date") be something like a "UNIQUE [INDEX]" instead....

Yeah seems that might be a lot more manageable than trying to force this.

[JonB]

@Stroopwafe1
Googling sqlite composite primary key there is a discussion about your situation at https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns
(there may be others). Read through it all. One post there says:

In another way, you can also make the two column primary key unique and the auto-increment key primary. Just like this: https://stackoverflow.com/a/6157337

Also https://www.sqlite.org/lang_createtable.html, at least the 3.5. The PRIMARY KEY section.

[Stroopwafe1]

Yes, thank you so much for your time and insight.
For anyone else that comes across this in a future search result: The solution is to just set a UNIQUE constraint, and add a column for autoincrement id.

The final SQL Table:

CREATE TABLE "FoodDay" (
	"id"	INTEGER NOT NULL,
	"food"	INTEGER NOT NULL,
	"date"	INTEGER NOT NULL,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	UNIQUE("food","date"),
	PRIMARY KEY("id" AUTOINCREMENT),
	CONSTRAINT "FK_FoodData_ID" FOREIGN KEY("food") REFERENCES "FoodData"("id")
)

[Stroopwafe1]

To add on to this, I discovered why it was trying to insert NULL for relation fields.
I did the select before setting my relations. And it wasn't enough to have a separate model load the related table. It has to be loaded in the QSqlRelationTableModel for it to work. So the very original comment here (sorry if this pings you) turned out to be 100% correct.
I'm just also posting this here for future reference

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

Isn't that actually a consequence of you using a QSqlRelationalTableModel? Isn't it that the Qt side expects to see in the related FoodData table/model an entry for the value of your food column in FoodDay table/model equal to it? You have "broken the contract" you establish with QSqlRelationalTableModel that Qt will see in-memory models maintaining the relationship? It is not good enough if you say the row does exist at the backend database side, if you want to use QSqlRelationalTableModel (you don't have to, it's only for value lookup) you must have the FK model/table up-to-date in memory.