On 12/21/16, Олег Пруц <olegp04...@gmail.com> wrote:
> Hello SQLite development team,
>
> I am contributing to DB Browser for SQLite (
> https://github.com/sqlitebrowser/sqlitebrowser). It is written in C++ and
> it relies on C API.
> We have a problem: when foreign_keys pragma is enabled, we cannot
> use sqlite3_prepare_v2.
>
> Steps to reproduce:
>
> Create database
> $ sqlite3 issue901.db
> SQLite version 3.15.2 2016-11-28 19:13:37
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE `a`(`id` integer);
> sqlite> CREATE TABLE `b`(`aid` integer, FOREIGN KEY(`aid`) REFERENCES
> `a`(`id`));
The parent column must be UNIQUE. I suggest changing the first table
definition to:
CREATE TABLE "a"("id" INTEGER PRIMARY KEY);
Note also that `...` identifier quoting is a deprecated MySQL-ism.
The perferred way to quote identifiers in SQL is with double-quotes:
"...".
> sqlite> INSERT INTO `a` VALUES(1);
> sqlite> .quit
> Minimal C program (issue901.c)
> #include <sqlite3.h>
> #include <stdio.h>
>
> #define NOTWORKING
>
> int main()
> {
> sqlite3* db;
> if(sqlite3_open_v2("issue901.db", &db, SQLITE_OPEN_READWRITE, NULL) !=
> SQLITE_OK)
> {
> printf("sqlite3_open_v2 failed!\n");
> return 1;
> }
>
> #ifdef NOTWORKING
> if(sqlite3_exec(db, "PRAGMA foreign_keys=\"1\"", NULL, NULL, NULL) !=
> SQLITE_OK)
> {
> printf("Enabling foreign keys failed!\n");
> return 1;
> }
> #endif
>
> sqlite3_stmt* stmt;
> if(sqlite3_prepare_v2(db, "UPDATE `a` SET `id`=7 WHERE `id`=1;", -1, &stmt,
> 0) != SQLITE_OK)
> {
> printf("sqlite3_prepare_v2 failed!\n%s\n", sqlite3_errmsg(db));
> return 1;
> }
>
> sqlite3_close(db);
> return 0;
> }
> Build and run
> $ gcc -lsqlite3 -o issue901 issue901.c
> $ ./issue901
> sqlite3_prepare_v2 failed!
> foreign key mismatch - "b" referencing "a"
>
> More detailed description here
> https://github.com/sqlitebrowser/sqlitebrowser/issues/901
>
> Regards,
> Oleg Prutz
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
