Re: Foreign Key Problems

ggrieff_at_netcom.com (Greg Grieff) writes:

> I must be missing something...please help?!
 

> I would like to create two tables, a parent table and a child table
> such that the child table will only accept rows for insert if there is a
> valid parent record that it references. Basically the text book primary
> key - foreign key relationship. Following is my SQL code to create the
> two tables.
 

> create table a (docid number(9) not null primary key,
> lname char(8),
> fname char(8))
> /
 

> create table b (imgid number(9) not null primary key,
> docid number(9) not null references a (docid) constraint
> docid_fk,
> filename char(8),
> sideid char(3))
> /

Actually, the syntax for a foreign key column constraint puts the constraint name before the references keyword, and naming the primary key column(s) is not strictly necessary; they're assumed by default. So

...

                docid number(9) not null constraint docid_fk references a,

> Now when I attempt to insert a record into b it will allow be to
> regardless of whether a valid parent record in a exists. What am I
> missing? Any help would be greatly appreciated.

You don't specify which version of Oracle you're using; version 6 accepts the syntax for foreign keys (exactly how correctly, I don't know), but doesn't do anything with it. Oracle server version 7 does do something with it, and it works fine on my system (I can't insert records into table b as described).

> Thanks in advance,
> -Greg
 

> -------------------------------------------------------------------------
> | Greg Grieff ggrieff_at_netcom.com | "Dream, Design, Develop, Debug, |
> | Head of Engineering | Deliver... Not necessarily in |
> | Micrographic Specialties Inc. | that order." |
> -------------------------------------------------------------------------

Hank Robinson,
Oracle DBA,
University of Warwick. Received on Mon Aug 08 1994 - 19:17:59 CEST