Repository navigation
[Bug]: Cannot add data to a table which exists #4169
Description
What did you do?
I have an issue where I can create new rows for some tables using slqitebrowser but it does not work for others.
I used sqlitebrowser to add records to a table called Competency using SQL from within sqlitebrowser. I added a record to the User table using the GUI.
I want to add a new row to a table called Evidence and it will use records from the tables mentioned previously as foreign keys.
Check that the table exists using select * from sqlite_schema;.
Result of this shows:
| type | name | tbl_name | rootpage | sql |
|---|---|---|---|---|
| table | Evidence | Evidence | 7 | CREATE TABLE "Evidence" ( "Id" INTEGER, "User_Id" INTEGER, "Competency_Id" INTEGER, "Evidence" TEXT, "Complete" INTEGER NOT NULL DEFAULT 0 CHECK("Complete" >= 0 AND "Complete" <= 1), PRIMARY KEY("Id" AUTOINCREMENT), CONSTRAINT "Competency_Id_FK" FOREIGN KEY("Competency_Id") REFERENCES "", CONSTRAINT "User_Id_FK" FOREIGN KEY("User_Id") REFERENCES "User"("Id") ) |
Attached the database in sqlitebrowser and ran the following SQL:
insert into evidence(User_Id, Competency_Id) values (1,3);
What did you expect to see?
I expected that I would be able to add a new record to the Evidence table.
What did you see instead?
Result of this query is
Execution finished with errors. Result: no such table: main. At line 5: insert into evidence(User_Id, Competency_Id) values (1,1);
Open sqlite command line interface. Same command completes successfully.
Open sqlitebrowser and I can see the data but cannot update or delete it using SQL from the application.
DB4S Version
3.13.1
What OS are you seeing the problem on?
MacOS
OS version
MacOs Tahoe v26.5.2
Relevant log output
Prevention against duplicate issues
- I have searched for similar issues
Activity
FriedrichFroebel commentedon Aug 1, 2026
Could you please provide the full steps to reproduce this issue from scratch?
chrisjlocke commentedon Aug 1, 2026
DB4S has a very weird quirk when it comes to the GUI and creating foreign references. I'll have to post a youtube video. I did post this in an earlier issue, but its probably long since died.
The short version is that DB4S has mangled the foreign key reference for you.
CONSTRAINT "Competency_Id_FK" FOREIGN KEY("Competency_Id") REFERENCES "",
The other constraint is correct - 'REFERENCES "Competency"("Id")'
The references bit is blank.
I can't easily explain the GUI thing, but in a video its a quick thing where you'll go, 'oh for f.... sake.....' and it'll work.
Alternatively, drop the contraint in SQL and add it manually.
I'll post the video later....
RichyL commentedon Aug 2, 2026
I set the database up a while ago and I do recall finding setting up the foreign key constraints a little challenging. I've fixed the Reference of the foreign key and it not works.
Thank you very much.
chrisjlocke commentedon Aug 2, 2026
Not or now? ;)
RichyL commentedon Aug 2, 2026
Sorry. It now works that I've fixed the reference in the foreign key constraint.