Question about foreign key

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Question about foreign key

Igor Korot
Hi, ALL,
According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
support is disabled by default.
In order to enable it I need to compile SQLITE with 2 defines undefined.

I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
added .c and .h file to my project
and inspected them.

SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
see the #define of this constant
anywhere in the .c file.

Is foreign key documentation outdated?

Also one minor question: do I need both .h files or just one will suffice?

Thank you.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Pavel Ivanov-2
On Sat, Oct 20, 2012 at 10:41 PM, Igor Korot <[hidden email]> wrote:
> Hi, ALL,
> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
> support is disabled by default.
> In order to enable it I need to compile SQLITE with 2 defines undefined.

Which also undefined by default. Any SQLITE_OMIT_* define is undefined
by default.

> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
> added .c and .h file to my project
> and inspected them.
>
> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
> see the #define of this constant
> anywhere in the .c file.
>
> Is foreign key documentation outdated?

Nope, everything is right.

> Also one minor question: do I need both .h files or just one will suffice?

Would be nice to know what names those both .h files have. But
generally speaking sqlite3.h should be enough.


Pavel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Keith Medcalf
In reply to this post by Igor Korot
On Saturday, 20 October, 2012 at 23:42, Igor Korot <[hidden email]> wrote:

> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
> support is disabled by default.

Yes, foreign key enforcement is disabled by default.

> In order to enable it I need to compile SQLITE with 2 defines undefined.

Not quite.  The default setting for the enforcement of foreign key constraints is controlled by the define

SQLITE_DEFAULT_FOREIGN_KEYS

The default value is 0, which disables foreign key constraint enforcement by default.  
Recompiling with a value of 1 enables foreign key constraint enforcement by default.

At runtime, you can use the SQL statements
PRAGMA FOREIGN_KEYS=1;
to enable foreign key constraint enforcement and
PRAGMA FOREIGN_KEYS=0;
to disable foreign key constraint enforcement.  
PRAGMA FOREIGN_KEYS;
will return 0 or 1 indicating whether foreign key constraints are being enforced or not.

> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
> added .c and .h file to my project and inspected them.
 
> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
> see the #define of this constant anywhere in the .c file.

By default nothing is omitted.  However, if you *do* choose to omit parts of the engine, the effect will be as described:

If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).  

> Is foreign key documentation outdated?

No, it is correct.  See http://www.sqlite.org/compile.html for the options you can define at compile time, the defaults, and the effect.

> Also one minor question: do I need both .h files or just one will suffice?

sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are not part of the engine itself -- that is, loadable modules.  sqlite3.h is the header file which must be included by c sources which are part of the engine, such as the sqlite3.c amalgamation itself, or extension modules that will be built-in.
 
> Thank you.


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Igor Korot
Hi, Keith,

On Sat, Oct 20, 2012 at 11:10 PM, Keith Medcalf <[hidden email]> wrote:
> On Saturday, 20 October, 2012 at 23:42, Igor Korot <[hidden email]> wrote:
>
>> According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
>> support is disabled by default.
>
> Yes, foreign key enforcement is disabled by default.

OK.

>
>> In order to enable it I need to compile SQLITE with 2 defines undefined.
>
> Not quite.  The default setting for the enforcement of foreign key constraints is controlled by the define
>
> SQLITE_DEFAULT_FOREIGN_KEYS
>
> The default value is 0, which disables foreign key constraint enforcement by default.
> Recompiling with a value of 1 enables foreign key constraint enforcement by default.
>
> At runtime, you can use the SQL statements
> PRAGMA FOREIGN_KEYS=1;
> to enable foreign key constraint enforcement and
> PRAGMA FOREIGN_KEYS=0;
> to disable foreign key constraint enforcement.
> PRAGMA FOREIGN_KEYS;
> will return 0 or 1 indicating whether foreign key constraints are being enforced or not.

All this contradicts the document I referenced above. See part 2 of it.

[quote]
In order to use foreign key constraints in SQLite, the library must be
compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER
defined.
[/quote]

The constant you mentioned is not present in this part of online documentation.

>
>> I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
>> added .c and .h file to my project and inspected them.
>
>> SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
>> see the #define of this constant anywhere in the .c file.
>
> By default nothing is omitted.  However, if you *do* choose to omit parts of the engine, the effect will be as described:
>
> If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

Well let me clarify a bit.
I am trying to develop a software which will communicate with SQLite
database. In order for that to happen properly I need the
foreing key constraint to be included.
And now I need to look for a third constant that was not in a picture. ;-)

>
>> Is foreign key documentation outdated?
>
> No, it is correct.  See http://www.sqlite.org/compile.html for the options you can define at compile time, the defaults, and the effect.

Well it does not mention the constant you were talking about so at the
very least it's incorrect/incomplete.

>
>> Also one minor question: do I need both .h files or just one will suffice?
>
> sqlite3ext.h is used when compiling extensions to the sqlite3 engine that are not part of the engine itself -- that is, loadable modules.  sqlite3.h is the header file which must be included by c sources which are part of the engine, such as the sqlite3.c amalgamation itself, or extension modules that will be built-in.

OK, so sqlite3.h should be enough to work with basic query. That's
what I thought but needed confirmation for.

Thank you.

>
>> Thank you.
>
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Simon Slavin-3

On 21 Oct 2012, at 7:35am, Igor Korot <[hidden email]> wrote:

> database. In order for that to happen properly I need the
> foreing key constraint to be included.

The simplest thing is to use any compilation (including any you already have) and just make sure you use the PRAGMA immediately after opening the file.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Igor Korot
Hi, Simon,

On Sun, Oct 21, 2012 at 12:05 AM, Simon Slavin <[hidden email]> wrote:
>
> On 21 Oct 2012, at 7:35am, Igor Korot <[hidden email]> wrote:
>
>> database. In order for that to happen properly I need the
>> foreing key constraint to be included.
>
> The simplest thing is to use any compilation (including any you already have) and just make sure you use the PRAGMA immediately after opening the file.

I didn't change anything yet and didn't include any defines in the MSVC project.
So all I need is "sqlite3_exec( handle, "PRAGMA foreign_keys = ON",
NULL, NULL, &error );", right?

Thank you.

>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Question about foreign key

Simon Slavin-3

On 21 Oct 2012, at 8:54am, Igor Korot <[hidden email]> wrote:

> On Sun, Oct 21, 2012 at 12:05 AM, Simon Slavin <[hidden email]> wrote:
>>
>>
>> The simplest thing is to use any compilation (including any you already have) and just make sure you use the PRAGMA immediately after opening the file.
>
> I didn't change anything yet and didn't include any defines in the MSVC project.
> So all I need is "sqlite3_exec( handle, "PRAGMA foreign_keys = ON",
> NULL, NULL, &error );", right?

Right.  Actually you should really examine the values that is returned and (simplest) assert that it's SQLITE_OK, but that's true for every _exec().

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users