Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » SQLite and foreign keys
SQLite and foreign keys [message #40977] Thu, 17 October 2013 02:28 Go to next message
wimpie is currently offline  wimpie
Messages: 46
Registered: March 2013
Location: holland
Member
Hello,

I am using SQLite, with a Schema with a few tables with references and I noticed I could delete rows in one table which were used in another.
After some digging I noticed in Sqlite3Schema.h the REFERENCES*** macro's are not defined.

First I wondered why, but after reading Sqlite doc's I think it's because it doesn't allow adding constraints to an existing table. Am I right?

But, it does allow it when creating the column, so I changed this in Sqlite3Schema.h:
#define REFERENCES(x)              INLINE_ATTRIBUTE("references " #x)

And as far as I can see, this works. I think it can also work with CASCADE ("references " #x " on delete cascade") but I did not test that.

But it seems not to be the perfect solution because I can't reference TABLE.COLUMN; Sqlite gives error on the ".". it needs TABLE(COLUMN) to work.
This can be put in the schema also like
TABLE (TEST)
	INT (ID) PRIMARY_KEY
	INT (KEY1) REFERENCES(SIMPLE_TEST1(ID))
END_TABLE

but I guess this is not portable with other SQL engines?

Can someone please comment on this?
Re: SQLite and foreign keys [message #40980 is a reply to message #40977] Thu, 17 October 2013 10:13 Go to previous messageGo to next message
chickenk is currently offline  chickenk
Messages: 169
Registered: May 2007
Location: Grenoble, France
Experienced Member
it seems that
REFERENCES TABLE(COLUMN)
is a portable construct: http://www.w3schools.com/sql/sql_foreignkey.asp
Re: SQLite and foreign keys [message #40981 is a reply to message #40977] Thu, 17 October 2013 10:20 Go to previous messageGo to next message
chickenk is currently offline  chickenk
Messages: 169
Registered: May 2007
Location: Grenoble, France
Experienced Member
Another note: foreign keys in sqlite3 are deactivated by default.

To have them enabled by default, one should define the CPP macro SQLITE_DEFAULT_FOREIGN_KEYS, see line 112284 of uppsrc/plugin/sqlite3/lib/sqlite3.c.

I use sqlite3 at work outside of U++, and my own solution is the following code (because I use a precompiled sqlite3 library), but I suppose defining SQLITE_DEFAULT_FOREIGN_KEYS is equivalent:

    int fkey_support;
    int sqlRet = sqlite3_open(path, &DB);
    /* [...] */
    if (sqlite3_db_config(DB, SQLITE_DBCONFIG_ENABLE_FKEY, 1, &fkey_support) != SQLITE_OK)
    {
        /* handle error */
    }
    printf("Foreign keys support is %s.\n", fkey_support?"ON":"OFF");
Re: SQLite and foreign keys [message #40983 is a reply to message #40981] Thu, 17 October 2013 10:33 Go to previous messageGo to next message
wimpie is currently offline  wimpie
Messages: 46
Registered: March 2013
Location: holland
Member
chickenk wrote on Thu, 17 October 2013 10:20

Another note: foreign keys in sqlite3 are deactivated by default.



You are right; I forget to mention, I use "PRAGMA foreign_keys = ON" in my program.
Re: SQLite and foreign keys [message #41292 is a reply to message #40977] Mon, 25 November 2013 20:05 Go to previous message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
wimpie wrote on Wed, 16 October 2013 20:28

Hello,

I am using SQLite, with a Schema with a few tables with references and I noticed I could delete rows in one table which were used in another.
After some digging I noticed in Sqlite3Schema.h the REFERENCES*** macro's are not defined.

First I wondered why, but after reading Sqlite doc's I think it's because it doesn't allow adding constraints to an existing table. Am I right?



Actually, it is rather because nobody bothered so far to implement it Smile

In fact, the issue is in RM for 2 years already:

http://www.ultimatepp.org/redmine/issues/172

I did not know there is problem with adding references later, if that is true, it really is kind of problem...

Mirek
Previous Topic: Strange build error with OleDB
Next Topic: testing out firebird in bazaar missing ibase.h
Goto Forum:
  


Current Time: Fri Mar 29 01:40:55 CET 2024

Total time taken to generate the page: 0.01297 seconds