QSQLITE problem with FOREIGN KEY

[kamilus]

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]

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]

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]

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 that does not exist.

[kamilus]

now i see the problem, thank you for help

[Diego.Oliveira]

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]

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)
);

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)
);

To copy to clipboard, switch view to plain text mode 

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)
);

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

To copy to clipboard, switch view to plain text mode