On Sat, 29 Dec 2007 15:17:27 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote:
> >Am 29.12.2007 um 13:59 schrieb Kees Nuyt: > >> >> Hi Lothar, >> >> On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens >> <[EMAIL PROTECTED]> wrote: >> >> The only implementation I'm aware of is the one using triggers, >> but the creation is not implemented as an extension. >> >> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers >> http://www.sqlite.org/contrib (second entry) >> http://www.justatheory.com/computers/databases/sqlite/ >> foreign_key_triggers.html >> http://www.rcs-comp.com/site/index.php/view/Utilities- >> SQLite_foreign_key_trigger_generator >> >>> Thanks, Lothar >> > >In general this will help. But I have encountered a problem: > >create table IF NOT EXISTS regressiontest ( > id int primary key, > test char(100) >); > >create table IF NOT EXISTS test ( > id int primary key, > id_reg int, > constraint fk_reg foreign key (id_reg) references regression (id) >); > >First problem (propably only in sqliteman): > >The statement could not be executed at once ?? Better try it in the sqlite3 command line tool. That's common ground to everyone of us. >Second problem: > >The constraint is really ignored. >(Referencing a non existent table) Indeed it is ignored. the syntax is parsed, that's all. >I have no idea how to catch this while creating the >tables (triggers are not possible on system tables). You have several options: 1- Add the required CREATE TRIGGER statements to the schema source by hand. (easiest, that's what I do) 2- Build a tool which parses the schema when you create a database and generates the required CREATE TRIGGER statements before you pipe the CREATE statements into the database. (relatively easy) 3- Build a tool which parses the schema when you open a database and CREATE the triggers if they aren't in place. (more difficult) 4- Change the SQLite source to implement foreign key constraints yourself. (very hard) 5- Wait until it is implemented in SQLite. Implementing foreign key constraints is on the ToDo list of the developers (my way) >I may create a consistency check while opening the database. That's too late, the database would already be inconsistent. You really need to implement the FK constraint, using triggers or otherwise. >Any ideas on this ? You could create a hook on every schema change (look for updates of sqlite_master in the sqlite library source) which checks if the required triggers are already CREATEd and if not, create them. >Thanks, Lothar HTH -- ( Kees Nuyt ) c[_] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
