Re: foreign keys

Am 24.11.2014 17:23, schrieb ruben safir:
>
> 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?

You have ESSN entries in WORKS_ON which do not exist as SSN in EMPLOYEE.

Try

SELECT essn

   FROM works_on
  WHERE essn NOT IN

       (SELECT ssn
          FROM employee);

If you create a constraint in the state of ENABLED an VALIDATED, all preexisting data must satisfy this constraint, otherwise the statement will fail.

Gruß
Peter

-- 
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you.                    -- David McCullough Jr.