How to create table with foreign key

[magicu]

1. I notice that QxBlog sample has no foreign key restrict, I can modify blog_id in comment table to any value which is not existed in blog table. How to create table with foreign key in QxOrm?

2. If I create table with sql in SQLite Expert, how to map the restrict to my code?

Thank you.

[QxOrm admin]

1. I notice that QxBlog sample has no foreign key restrict, I can modify blog_id in comment table to any value which is not existed in blog table. How to create table with foreign key in QxOrm?

qxBlog tutorial uses qx::dao::create_table<T>() function to create a prototype (or a quick example).
For a real application, I don't recommend to use qx::dao::create_table<T>() function : it must be used only to create a prototype with SQLite.
I strongly recommend to build your SQL schema with a tool provided by your SGBD (SQLite Expert or SQLite Manager for example in your case).
But if you prefer, you can also create automatically your own SQL schema using introspection engine of QxOrm library, here is the FAQ to do it :
http://www.qxorm.com/qxorm_en/faq.html#faq_230

Then, it is your choice
You can consider that all relationships one-to-many and many-to-one need a foreign key.
Or if you want to have foreign key only for specifics relations, you can use the notion of property bag (adding some meta-data to your relation) :
viewtopic.php?f=2&t=81#p370

If I create table with sql in SQLite Expert, how to map the restrict to my code?

The mapping doesn't change in your C++ code, because it's your choice and your design to add or not foreign key constraint in your SQL schema.
From a C++ point of vue, it's the same thing.
You can just add some meta-informations using the notion of property bag if you want...

[magicu]

The mapping doesn't change in your C++ code, because it's your choice and your design to add or not foreign key constraint in your SQL schema.
From a C++ point of vue, it's the same thing.

That's not exactly what I want. I need a error throwed when I insert a not existed foreign id. Can it be achievable?

[QxOrm admin]

I need a error throwed when I insert a not existed foreign id. Can it be achievable?

Yes, but I repeat : you must define your constraints into your database (SQL schema), not into your C++ mapping.
Then, when you have defined your constraints into your database, your database must return an error when a constraint is not valid (invalid foreign key for example).
Using QxOrm, you can check QSqlError returned by all qx::dao::... functions.

If you want exceptions thrown using QxOrm, you can use the notion of session : qx::QxSession class (http://www.qxorm.com/doxygen/html/class ... ssion.html).
You can find a documentation about session here : http://www.qxorm.com/qxorm_en/faq.html#faq_170

Note : a session can throw a qx::dao::sql_error exception when a SQL error occured (by default, there is no exception). You can setup this feature using :
* qx::QxSession constructor (for a specific session) ;
* qx::QxSqlDatabase::getSingleton()->setSessionThrowable(bool b) parameter (for all sessions).

Then you can write something like this :

Code: Select all

try
{
  qx::QxSession session;
  session.insert(my_object);
  session.update(my_object);
  session.fetchById(my_object);
  session.deleteById(my_object);
}
catch(const qx::dao::sql_error & err)
{
   //a SQL error occured...
}

[magicu]

I need a error throwed when I insert a not existed foreign id. Can it be achievable?

Yes, but I repeat : you must define your constraints into your database (SQL schema), not into your C++ mapping.
Then, when you have defined your constraints into your database, your database must return an error when a constraint is not valid (invalid foreign key for example).
Using QxOrm, you can check QSqlError returned by all qx::dao::... functions.

If you want exceptions thrown using QxOrm, you can use the notion of session : qx::QxSession class (http://www.qxorm.com/doxygen/html/class ... ssion.html).
You can find a documentation about session here : http://www.qxorm.com/qxorm_en/faq.html#faq_170

Note : a session can throw a qx::dao::sql_error exception when a SQL error occured (by default, there is no exception). You can setup this feature using :
* qx::QxSession constructor (for a specific session) ;
* qx::QxSqlDatabase::getSingleton()->setSessionThrowable(bool b) parameter (for all sessions).

Then you can write something like this :

Code: Select all

try
{
  qx::QxSession session;
  session.insert(my_object);
  session.update(my_object);
  session.fetchById(my_object);
  session.deleteById(my_object);
}
catch(const qx::dao::sql_error & err)
{
   //a SQL error occured...
}

I got it.