This is probably slightly off-topic, but here's what we did in LedgerSMB 1.3 to address this problem. It would be a little more complicated in SQL-Ledger but probably someone could do it without any real difficulty as the same subset of information would be of interest.
Basically you can create a table whose sole purpose is to maintain referential integrity. CREATE TABLE transactions (id int, table_name string); table_name is kind of optional but it makes Then you can add triggers to maintain rows in the transactions table from (at least, depending on how far you want to take this): ar ap gl Triggers should work on insert, update, and delete. Then you can add foreign keys against that table, something like: alter table ar add foreign key (id) references transactions(id); alter table gl add foreign key (id) references transactions(id); alter table ap add foreign key (id) references transactions(id); alter table acc_trans add foreign key (trans_id) references transactions (id); Then you can be sure three things: 1) When you insert data it will connect in a meaningful way, and 2) You can't update the id field or delete the record in a way that leaves acc_trans records orphanned. 3) ID's can't be re-used between these tables. I'd be happy to post code if folks are interested. One note: You can't use rules instead of triggers. We learned this the hard way in 1.2 :-) Best Wishes, Chris Travers _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
