PDA

View Full Version : QSQLITE problem with FOREIGN KEY



kamilus
24th April 2011, 20:51
hi everybody, i have a problem, when i compile such a code:

query.exec("CREATE TABLE IF NOT EXISTS ProductTypeGroup(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
query.exec("CREATE TABLE IF NOT EXISTS ProductType(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, low_level INTEGER )");

everything is ok, the table called ProductType will be created

but when i compile such a code:

query.exec("PRAGMA foreign_keys = ON;");
query.exec("CREATE TABLE IF NOT EXISTS ProductTypeGroup(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
query.exec("CREATE TABLE IF NOT EXISTS ProductType(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, low_level INTEGER , FOREIGN KEY (group) REFERENCES ProductTypeGroup(id)");

i don't have any errors but table called ProductType won't be created

Do You know what can be a problem??

Kangs
24th April 2011, 21:38
hi everybody, i have a problem, when i compile such a code:

query.exec("CREATE TABLE IF NOT EXISTS ProductTypeGroup(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
query.exec("CREATE TABLE IF NOT EXISTS ProductType(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, low_level INTEGER )");

everything is ok, the table called ProductType will be created

but when i compile such a code:

query.exec("PRAGMA foreign_keys = ON;");
query.exec("CREATE TABLE IF NOT EXISTS ProductTypeGroup(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
query.exec("CREATE TABLE IF NOT EXISTS ProductType(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, low_level INTEGER ), FOREIGN KEY (group) REFERENCES ProductTypeGroup(id)");

i don't have any errors but table called ProductType won't be created

Do You know what can be a problem??
You have certainly errors, test the return value of QSqlQuery::exec
You foreign key is bad (not exists).

kamilus
24th April 2011, 23:12
yes You' re right the first query.exec return true and the second (this with FOREIGN KEY) return false, but the whole project is compiling. Have you got any idea what i do wrong?

DanH
25th April 2011, 03:04
The fact that it compiles proves nothing. You could have utter gibberish in the query strings and it would still compile without error.

First off, you need to develop the habit of checking every return code from SQL, even if just with an assert.

Then, if you have trouble with a given SQL statement and the cause isn't immediately obvious, the thing to do is to use the sqlite3 command line tool to test the statement and slowly add/remove/change things until you find your error.

In your case, it appears that you have specified a child key (http://www.sqlite.org/foreignkeys.html#parentchild) that does not exist.

kamilus
25th April 2011, 13:25
now i see the problem, thank you for help

Diego.Oliveira
25th April 2011, 14:24
Hello

Try running the query in this way.

"CREATE TABLE IF NOT EXISTS ProductType(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, low_level INTEGER , FOREIGN KEY (group) REFERENCES ProductTypeGroup(id))"

att

Diego Oliveira

ChrisW67
26th April 2011, 01:10
Here are the problems I can see in theProductType create table:

A stray closing parentheses
The column 'group', used in the foreign key constraint, does not exist in table ProductType
The name 'group' is a reserved word anyway


Here's a working one with the foreign key as a table constraint:


CREATE TABLE ProductType(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
low_level INTEGER,
prod_group INTEGER NOT NULL,
FOREIGN KEY (prod_group) REFERENCES ProductTypeGroup(id)
);
or as a column constraint:


CREATE TABLE ProductType(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
low_level INTEGER,
prod_group INTEGER NOT NULL REFERENCES ProductTypeGroup(id)
);