Re: Foreign Keys

From: ddf <oratune_at_msn.com>
Date: Mon, 1 Dec 2014 05:46:52 -0800 (PST)
Message-ID: <701d15b8-5f48-41d8-bc58-99a05b162f27_at_googlegroups.com>

On Monday, November 24, 2014 9:25:56 AM UTC-7, ruben safir wrote:
> I have this very simple practice database is 11g and I can't add a foreign
> key to a table. Maybe someone can tell me what I'm doing wrong.
>
> I have table one
>
> create table employee(
> fname varchar2(20) NOT NULL, minit char(1),
> lname varchar2(20) NOT NULL, ssn number(9) NOT NULL,
> bdate date NOT NULL,
> address varchar2(50) NOT NULL,
> sex char(1) NOT NULL,
> salary float(12) NOT NULL, superssn number(9),
> dno number(2)
> );
>
> I set a primary key
>
> alter table employee add constraint pk_employee primary key (ssn)
> /
>
> I create table two
>
>
> create table works_on(
> essn number(9),
> pno number(2), hours float(5) );
>
>
> ALTER TABLE WORKS_ON ADD CONSTRAINT workson_essn FOREIGN KEY (ESSN)
> REFERENCES EMPLOYEE (SSN);
>
> SQL> _at_fk_workson_essn.sql ADD CONSTRAINT workson_essn
> *
> ERROR at line 2:
> ORA-02298: cannot validate (RUBEN.WORKSON_ESSN) - parent keys not found
>
> what is the error?
>
> --
> The Coin Hangout: http://www.coinhangout.com/home

Using your code on 11.2.0.4 I have no problems:

SQL> create table employee(

  2          fname varchar2(20) NOT NULL, minit char(1),
  3          lname varchar2(20) NOT NULL, ssn number(9) NOT NULL,
  4          bdate date NOT NULL,
  5          address varchar2(50) NOT NULL,
  6          sex char(1) NOT NULL,
  7          salary float(12) NOT NULL, superssn number(9),
  8          dno number(2)
  9          );

Table created.

SQL>
SQL> alter table employee add constraint pk_employee primary key (ssn)   2 /

Table altered.

SQL>
SQL> create table works_on(

  2                  essn number(9),
  3                  pno number(2), hours float(5) );

Table created.

SQL>
SQL> ALTER TABLE WORKS_ON ADD CONSTRAINT workson_essn FOREIGN KEY (ESSN)   2 REFERENCES EMPLOYEE (SSN); Table altered.

SQL> My original assessment still stands -- there is data in the child table that isn't matched in the parent.

David Fitzjarrell Received on Mon Dec 01 2014 - 14:46:52 CET