Re: How to ensure data consistency?

On Wed, 08 Sep 2004 09:03:30 -0700 ddtl wrote:
> >> If that is so, it won't work (try it!).
> >
> >I just tried it: It seems to work (didn't try implementing any checks,
> >though). What makes you believe that it doesn't?
>
> I just wonder how new rows are going to be added to the main table -
> before you can insert anything into it, you have to fill in data into
> the seconday tables (otherwise, if they are empty, FK constraints in the
> main table will prevent adding rows, because FK fields have to reference
> an already existing data).

Yes, the row for the secondary table has to be filled in before entering the corresponding row to the main table.

> Suppose you did just that, and want to insert the first item into the
> main table, and suppose that thit item belongs to a group 'b'. You
> fill in item's properties, and set b_id to one of the values in
> BS.b_id. But what do you do with TS.a_id, TS.c_id .... TS.N_id?

I set them to NULL and I don't see why this should be a problem. That TS.b_id is non null can be validated using a CHECK similar to the following (proposed by Tony somewhere in this thread):

CHECK ((type = 'A' AND a_id IS NOT NULL) OR (type != 'A' AND a_id IS NULL))
> Besides, the same problem with the data integrity exists here - how can you
> ensure that the user, after inserting a new row into a sub-table, will update
> the main table accordingly?

To avoid orphaned rows in subtables, it seems necessary to set up circular foreign key constraints.

Anyways, the above solution seems to be inferior to that proposed by Lennart (in this thread) and that by Joe Celko [1] (that's the one that Dan referred to, I guess). So further discussion of my proposal is not necessary, IMO.

Felix

[1] http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=OEYKH2bbEHA.368%40TK2MSFTNGP10.phx.gbl&rnum=5 Received on Thu Sep 09 2004 - 01:46:28 CEST