U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » SQLite and foreign keys

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

SQLite and foreign keys [message #40977]

Thu, 17 October 2013 02:28

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?

Report message to a moderator

Re: SQLite and foreign keys [message #40980 is a reply to message #40977]

Thu, 17 October 2013 10:13

chickenk
Messages: 171
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

Report message to a moderator

Re: SQLite and foreign keys [message #40981 is a reply to message #40977]

Thu, 17 October 2013 10:20

chickenk
Messages: 171
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");

Report message to a moderator

Re: SQLite and foreign keys [message #40983 is a reply to message #40981]

Thu, 17 October 2013 10:33

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.

Report message to a moderator

Re: SQLite and foreign keys [message #41292 is a reply to message #40977]

Mon, 25 November 2013 20:05

mirek
Messages: 14255
Registered: November 2005

Ultimate Member

wimpie wrote on Wed, 16 October 2013 20:28

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Fri Apr 25 12:46:04 CEST 2025

Total time taken to generate the page: 0.00789 seconds