Re: How to implement relationships between tables

"Sönke Petersen" <sk.petersen_at_gmx.de> wrote in message news:b76d6p$9l1$1_at_news.mch.sbs.de...
> Hello there,
>
> I must admit most knowledge about databases comes from working with MS
> Access.
> I understood that relationships between between tables are implemented by
> constraints: foreign keys etc.
>
> But I have to deal with these keys also on an application level.
> Or do I not?
>
> For example: if I use an ordinary integer value as primary and foreign
key
> to implement
> a relationship. Is there an autoincrement operation if one executes an
> update operation
> on one of the tables?
>
> How can I define such an autoincrement?
>
> A similar question arises in the case of deleting records. What if I
delete
> records with the
> highest used integer values. Does Oracle track the used counters?
>
> So there a lot more questions I have and I'd like to ask you around here
if
> there is info
> which describes in a detailled manner the implentation of relationships
> within Oracle.
>
> Regards,
> Sönke

Hm; not sure how to answer these. Usually your candidate key appears in all tables which require it. Are you trying to reduce a 5-field key into a 1-field numeric key? Oracle has a "hidden" rownum column that it uses internally, but, I don't think that's what you're asking for, since it will be different across tables. Unfortunately, you'll have to "un-learn" Access; try to forget it even existed. Try looking for the book named "Oracle 9i: Beginners Guide", by Michael Abbey, published by Osborne. It should answer most of your questions about Oracle. -Most- of the beginners guide (7.x, 8.0, 8i, 9i) books' text are compatible between versions, so if you have 8i on your server, you'd be fine buying a 9i book. An 7.x guide would work well as a true beginner's guide for 8i, etc.

I can half-answer one question of yours, though. You can handle your keys and constraints on an application level, or, let Oracle handle them- or both! Obviously, if Oracle handles them, your system becomes more poweful, however, your application will need to recognize errors from Oracle. You could even write PL/SQL blocks (stored and executed by Oracle) to handle DML ops (update/insert/delete) on tables, which will also make your system more powerful and efficient.

Hope that helps,

-Thomas Received on Fri Apr 11 2003 - 11:46:20 CDT