Re: Insert with a relationship...

In article <8DYY4.224338$Tn4.1963848_at_news1.rdc2.pa.home.com>, rfazio_at_home.com.nospam says...
> All assumptions are correct about relationships, but in order for anything
> to automatically update the address with the correct field, someone has to
> code the correct insert statement into the address table. If you are using
> something like Oracle forms to create the screen, then maybe (I don't use
> Oracle forms). But for anyone to answer this question, we will need to know
> what tool you are using.
>

Bob - thanks a bunch. I'm using VB/ADO, and trying to use as much PL/SQL as I can. I know I'm missing something here, but if I have to manually code inserts for ALL fields, then why the *$#@ do I have a relationship to begin with? I can guess the following:

• it looks good on the schema diagram, knowing it's been somewhat normalized..
• it may (I don't know) generate SQL runtime errors if the CHILD field contains a non-matching (or null) parent field..
• it may be good for cascading deletes (if I deleted Georgia ("Ga") then the cascade should go out and find all the children and ice them)

But during the insert then, it does nothing other than generat an error with a mis-match or failed lookup - which I still have to trap for in my app?

Can you tell I'm really new at this?

Alex

> --
> Robert Fazio, Oracle DBA
> rfazio_at_home.com
> remove nospam from reply address
> http://24.8.218.197/
> "GAB" <news4_at_REMOVEbaxnet.net> wrote in message
> news:MPG.139e1af3c3a43a51989690_at_news.usenetserver.com...
> > I'm new to this - so here goes.
> >
> > Table STATE is the "one" (on the one side of the one-many relationship),
> > with pri key column 'PK_State_ID'. Cool. Table ADDRESS is the "many",
> > with foreign key column 'FK_State_ID' referring back to 'PK_State_ID'.
> > STATE --> ADDR
> >
> > Am I even close with the following assumptions: ?
> > 1) Make a user screen with all the Address info, have them fill it in.
> > 2) When they get to the "State" field, use a drop-down and force them to
> > find a valid State. (BTW - Georgia is not a valid state of any kind,
> > neither is Alabama for that matter lol).
> > 3) If "State" does not exist, pop up another screen to add correct
> > state. Insert it. Now it should show up in #2.
> > 3) Now I've got enough info to insert an ADDR record I guess.
> >
> > Here's my big puzzling question:
> >
> > If I fill in the SQL statement with all the ADDR data from my screen,
> > will it then AUTOMATICALLY fill in the STATE field somehow since I've
> > ensured the FK/PK constraint in ADDR? Or do I have to do manually add
> > it to the insert statment, something like "insert into ADDR (street,
> > city, stateid) values (screen.street, screen.city, STATE.ID)".
> >
> > In other words, how much is storage/retrieval is done for you during
> > either the SELECT or INSERT due to the fact the constraint exists?
> >
> > I know this is awfully basic - sorry in advance
> >
> > Alex
> >
> >
> >
>
>
>
Received on Tue May 30 2000 - 00:00:00 CDT