Mike,

This all just sounds like bad database design. Normally one would construct
an ER (Entity Relationship) model, which would list your entities, their
attributes and the relationship(s) between them.

You would then use this to create your database.

When you start running into problems inserting data, getting data out,
writing queries in general, or creating constraints, it's usually (about 99%
IMHO) due to poor/non-optimal table design.

Perhaps if you could tell us what your entities are, and how they are
related to each other, we could suggest a more normalised table structure
which would avoid the problems you are having at the moment.

Cheers
Ken

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


: 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'.

Ken,
Much as it hurts to admit, you're probably right.  This is my first db, and
I started designing it long before reading about ER diagrams or normalizing,
and never went back to normalize it since it seemed ok with much testing in
the Access version.  It's grown far more complex than I originally had in
mind (featureitis) and I think now is a good time to take a hard look at the
db structure when I'm just starting to convert to sql server.

Thanks for the offer, but I'll try normalizing it myself first - mainly
because this is in large part a learning project for me, and I think I'll
never really understand normalizing unless I work through it.  Since I have
no schedule and no budget (personal project), I'll just keep working away on
it.

I'll also work on building those create-table scripts you mentioned, so I
can ease this question-answer process in the future.

Btw, today I tried using non-key fields in the scenario below, and was able
to insert into msde without error.  I'm worried that approach will make the
db non-rubust, so I'm going to take a hard look at it and see if I can make
it work with key fields - maybe using more than one intermediate table.

Regards,
Mike


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


> Mike,
>
> This all just sounds like bad database design. Normally one would
construct
> an ER (Entity Relationship) model, which would list your entities, their
> attributes and the relationship(s) between them.
>
> You would then use this to create your database.
>
> When you start running into problems inserting data, getting data out,
> writing queries in general, or creating constraints, it's usually (about
99%
> IMHO) due to poor/non-optimal table design.
>
> Perhaps if you could tell us what your entities are, and how they are
> related to each other, we could suggest a more normalised table structure
> which would avoid the problems you are having at the moment.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Mike" <mg188@c...>
> Subject: [asp_database_setup] Re: referential integrity question
>
>
> : 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'.
>
>
>
%%email.unsub%%
>
>