Re: Foreign Key Problems

Your syntax appears to be Oracle Version 6 (it's been a long time since I wrote V6 constraints). In V6, Oracle does not enforce constraints, so you can add a detail record without a master record.

If you're using Oracle 7, then the following is one way to create the two tables so that Oracle enforces the foreign key. The tables use table constraints, you can use column constraints instead. The not null constraints on the primary keys are optional since Oracle automatically enforces not null on the primary key column.

CREATE TABLE a

    (docid NUMBER(9)

         CONSTRAINT a_nn_docid NOT NULL,
     lname    CHAR(8),
     fname    CHAR(8),
     CONSTRAINT a_pk PRIMARY KEY (docid))

    (imgid NUMBER(8)

         CONSTRAINT b_nn_imgid NOT NULL,
     docid    NUMBER(9)
         CONSTRAINT b_nn_docid NOT NULL,
     filename CHAR(8),
     sideid   CHAR(3),
     CONSTRAINT b_pk PRIMARY KEY (imgid),
     CONSTRAINT b_fk_docid FOREIGN KEY (docid)
         REFERENCES a (docid))

Yee Lew
Oracle DBA/Programmer Analyst
Civilan working for the Air Force
Internet: lew_at_oodis01.hill.af.mil

SELECT disclaimer FROM std_disclaimer WHERE disclaimer = 'Opinions

    expressed are mine and does not reflect those of my employer.'

Greg Grieff (ggrieff_at_netcom.com) wrote:
: 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))
: /
 

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

: 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." |
: -------------------------------------------------------------------------
  Received on Mon Aug 08 1994 - 06:27:50 CEST