Re: How to ensure data consistency?

From: Tony Andrews <andrewst_at_onetel.com>
Date: 8 Sep 2004 02:35:39 -0700
Message-ID: <chmjpb$kl6@odbk17.prod.google.com>

Felix E. Klee wrote:
> What I still need, however, is a way to avoid "orphaned" entries in
the
> specialization tables. Using my proposed data model, it would be
> possible to add such entries without having an entry in the main
table.
> But, an entry in the main table cannot be made without an appropriate
> entry in the corresponding specialization table (foreign key
> constraints!).
>
> A solution for the latter problem, I guess, is to build a UI whose
> backend always creates rows for the specialization table *and* the
main
> table.

As Dan says elsewhere, the 2-way foreign keys will work if your DBMS allows constraint checking to be deferred until you commit the transaction:

/* Assume tables initially empty */

insert into parent (parent_id, child_id) values (1, 100);

/* No child with child_id=100 exists, but FK constraint is not checked yet */

insert into child (parent_id, child_id) values (1, 100); commit;
/* Deferred FK is now checked and succeeds */ Received on Wed Sep 08 2004 - 04:35:39 CDT