I have tables related in a star pattern (see rel1.gif).  Table4 is the hub
table, as you can see.  Rel2.gif shows a typical relationship between it and
each of the other tables.

The hub table just links the keys of the other tables.  This lets me show
values from  tables 1, 2 and 3 by querying against a field in one of these.
The queries work ok in both Access, and when I upsize to msde and an
Access.adp.

Access lets me insert a record to one of these tables without a
corresponding record in the other tables. Here's the asp code to insert:
objcon.execute "insert into tbl2(val1) values("&var1&")"


 But when I try this in msde through the Access.adp file, I get the error:

 Microsoft OLE DB Provider for SQL Server error '80040e14'
The record can't be added or changed. Referential integrity rules require a
related record in table 'Table1'.

Any advice would be appreciated.

Mike

Hi,

Your "images" didn't make it to the list. Can you provide a CREATE TABLE
script so that we can recreate your tables in one of our databases? Or
similar (maybe put your GIFs up on the web somewhere)?

That said, it seems like your constraints are somehow mixed up - for some
reason inserting into Table2 requires a "parent" record in Table1, which
shouldn't be happening if Table4 is the parent table.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Mike" <mg188@c...>
Subject: [asp_database_setup] referential integrity question


: I have tables related in a star pattern (see rel1.gif).  Table4 is the hub
: table, as you can see.  Rel2.gif shows a typical relationship between it
and
: each of the other tables.
:
: The hub table just links the keys of the other tables.  This lets me show
: values from  tables 1, 2 and 3 by querying against a field in one of
these.
: The queries work ok in both Access, and when I upsize to msde and an
: Access.adp.
:
: Access lets me insert a record to one of these tables without a
: corresponding record in the other tables. Here's the asp code to insert:
: objcon.execute "insert into tbl2(val1) values("&var1&")"
:
:
:  But when I try this in msde through the Access.adp file, I get the error:
:
:  Microsoft OLE DB Provider for SQL Server error '80040e14'
: The record can't be added or changed. Referential integrity rules require
a
: related record in table 'Table1'.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Ken,

Sorry about the images.  I deleted them already.  And now I'm having trouble
getting "create database" to work through msde - connection problem I
think...

Hmmm.  I think Table 4 is the child since it's only fields hold the primary
keys of the others.  And a child record can't be created unless all parent
foreign keys exist?  Is that how it works?

Here are the biz rules for a new record in table 4 (rec1 is the key in
table1, etc, and Rec4 holds rec1, rec2, and rec3):

Rec2 MAY be associated with rec3
Rec1 MUST be associated with rec2 OR rec3 OR (rec2 AND rec3)

So sometimes I need to insert a Rec4 that only has 2 values, but my current
design won't let me - I get the SQL Server error in my first post.

Would a good solution be to use a non-key field in each of tables 1,2,3 and
put a unique value in it for each record, then have Table 4 refer to these?
Seems a little clunky since each time I want to add a record, I'd have to
first select the max value of the new field, and then add 1.  Also, this
would mean changing about 20 asp files and a whole bunch of sprocs.

Thanks,
Mike

----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Database Setup" <asp_database_setup@p...>
Sent: Sunday, September 29, 2002 6:32 PM
Subject: [asp_database_setup] Re: referential integrity question


> Hi,
>
> Your "images" didn't make it to the list. Can you provide a CREATE TABLE
> script so that we can recreate your tables in one of our databases? Or
> similar (maybe put your GIFs up on the web somewhere)?
>
> That said, it seems like your constraints are somehow mixed up - for some
> reason inserting into Table2 requires a "parent" record in Table1, which
> shouldn't be happening if Table4 is the parent table.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Mike" <mg188@c...>
> Subject: [asp_database_setup] referential integrity question
>
>
> : I have tables related in a star pattern (see rel1.gif).  Table4 is the
hub
> : table, as you can see.  Rel2.gif shows a typical relationship between it
> and
> : each of the other tables.
> :
> : The hub table just links the keys of the other tables.  This lets me
show
> : values from  tables 1, 2 and 3 by querying against a field in one of
> these.
> : The queries work ok in both Access, and when I upsize to msde and an
> : Access.adp.
> :
> : Access lets me insert a record to one of these tables without a
> : corresponding record in the other tables. Here's the asp code to insert:
> : objcon.execute "insert into tbl2(val1) values("&var1&")"
> :
> :
> :  But when I try this in msde through the Access.adp file, I get the
error:
> :
> :  Microsoft OLE DB Provider for SQL Server error '80040e14'
> : The record can't be added or changed. Referential integrity rules
require
> a
> : related record in table 'Table1'.