On Jan 23, 2008, at 10:07 PM, [EMAIL PROTECTED] wrote:
I have been trying to implement the paradigm of using Triggers to
emulate referential integrity, for example cascading updates and
deletes between two database tables. This works when the two
database tables are in “main” but when I try to create the triggers
between database tables in attached database tables, the create
doesn’t work. Tried several iterations and couldn’t come up with the
proper SQL syntax to do this. Is there a way to do this, add
referential integrity triggers with database tables in attached
databases?
Even better, if/when is SQLite going to support built-in
referential integrity using foreign key constraints in the SQL when
creating
the tables? Can it support referential integrity with attached
database tables?
Triggers between two separate databases are not allowed since if
you DETACH one of the databases, the triggers obviously will no
longer work. If two separate databases are so inseparably bound
that they need triggers between them, why not just make them a
single database?
The same goes for foreign key constraints. There are *severe*
implementation difficulties trying to get this to work across
separate database. If you have a foreign key in a separate
database, that really argues that the two databases ought to
be one.
Example use case: “main” containing an “AccountTable”, and a daily
transactional table “YYYY-MM-DD.db3” that will be attached to “main”
that contains a TransactionTable that has a “foreign key relation”
to the “AccountTable” by having an account primary key as a foreign
key in the transaction table.
----------------------------------------------------------------------
-------
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------
-------
D. Richard Hipp
[EMAIL PROTECTED]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
