Re: Creating relationships in Oracle

Nicole Gryler wrote:

> How does one create a relationship from one table to another in Oracle?
> MS Access makes it very simple, and I'm trying to learn Oracle. I
> understand that one must create Foreign Keys to reference Primary Keys
> in the target table, etc. But I'm sure I'm missing more - I'm using
> Oracle 8 on Netware.
>
> John Harris
> College of Nursing, U of Utah
> john.harris_at_nurs.utah.edu

No, you're not missing anything. That's how you do it. Oracle, being the flexible beast that it is, offers several different syntaxes (is there such a word?) for doing this. You can include the Foreign Key definition in the Create Table statement either as an extension of the column definition or as part of the table defintion, or you can do it via a separate Alter Table statement. The latter is probably the best method so that you can apply it after all the tables have been created. If you use the former methods you must ensure that the referenced table (parent table) is created first. In case you don't have a manual here are simple examples: (assume col1 is the primary key in t1)

create table t2(
...
my_ref_col number constraint fk_t2 foreign key references t1(col1), ...
... more columns)
/

create table t2(
...
my_ref_col number,
...
... more columns,
constraint fk_t2 foreign key fk_t2 references t1(col1) )
/

alter table t2 add (
constraint fk_t2 foreign key fk_t2 references t1(col1) )
/

Note that there are other optional parameters which can be included in the FK definition such as index storage and how to handle deleting of the parent record.

If you have any further questions about this feel free to email me.

Geoff Received on Mon May 03 1999 - 21:11:53 CDT