Enabling the foreign key support in Sliqte3

[Luciano de Souza <luchyanus@...>]

Hi all,

I want to use foreign keys in Sqlite3. I am using the modules available 
in Lua for Windows and I downloaded the DLL and the commandline tool for 
Sqlite3.

With Sqlite3.7.5, reading the manual instructions, I understand I should 
enable the foreign key support with this command:

pragma foreign_keys = on;

Yes, the return of the command "pragma foreign_keys;" goes from 0 to 1. 
The foreign key support is really enabled. In spite of this, I can 
insert new elements disregarding this constraint.

observe firstly an example:


begin transaction;

create table annotation_master
(
id integer primary key not null,
entry datetime not null,
cathegory integer not null,
subject varchar(40) not null,
description text not null,
foreign key(cathegory) references annotation_cathegory(id)
);

create table annotation_cathegory
(
id integer primary key not null,
title varchar(30) not null
);

commit;

Now, the Lua script used to insert new elements:

require('luasql.sqlite3')
require('classlib')
require('date')

module('dbr', package.seeall)

filename = 'calau.dbl'

class.annotation()

function annotation:__init()
obj = self
self.env = luasql.sqlite3()
self.con = self.env:connect(filename)
self.cur = self.con:execute('select * from annotation_master')
end

function annotation:quit()
obj.cur:close()
obj.con:close()
obj.env:close()
end

function annotation:append(cathegory, subject, description)
local err, msg = obj.con:execute(string.format("insert into 
annotation_master(entry, cathegory, subject, description) 
values(datetime('now'), %d, '%s', '%s')", cathegory, subject, description))
return err, msg
end

reg = annotation()
err, msg = reg:append(3, 'Subject 1', 'Description 1')
print(msg)
reg:quit()

Does someone has an idea why I can't use foreign keys?

Luciano de Souza