On Tue, Oct 18, 2011 at 4:58 PM, Wallace Roberts <[email protected]> wrote: > On Tue, Oct 18, 2011 at 4:48 PM, Chris Travers <[email protected]> > wrote: > ... >> Basically you can create a table whose sole purpose is to maintain >> referential integrity. > > OK, I'm a bit confused here... isn't referential integrity a feature > of a real DBMS that is setup via constraints? Are you saying that > PostgreSQL doesn't support this feature, or that the SQL-Ledger design > doesn't employ it?
The problem is that the SQL-Ledger database design is such that you can't just use constraints to do this. As you will note, my solution includes such constraints. However, here is the problem (yes it affects LSMB through 1.3 too): acc_trans.trans_id is a foreign key. It references, in practice: select id from ar union select id from ap union select id from gl; So you don't have a good way of just defining a foreign key on that table as it is. I don't see a good way of doing this absent adding an additional table in the middle to deal with this ambiguity. The ambiguity is actually quite a bit more substantial than this when we start looking at the shipto table but that's usually a bit less critical. Best Wishes, Chris Travers _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
