Re: Simple foreign key question

Hi Sean,

Here's a sample trigger idea..it assumes two identical tables EMP and EMPTEST (with a nonunique department number (deptno)). When the user attempts an insert or update on emptest in this example, the trigger queries the other table with the nonunique deptno (emp).

If the department number doesn't exist in the other table an error is returned..

CREATE OR REPLACE TRIGGER integ
before insert or update on emptest
FOR EACH ROW DECLARE
V_DEPTNO NUMBER; BEGIN
SELECT DISTINCT DEPTNO
INTO V_DEPTNO
FROM EMP
WHERE DEPTNO = :new.deptno;

EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20501,'Department must exist in EMP table. ');

END;
/

SQL> insert into emptest(empno,deptno)
  2 values(9999,70);
insert into emptest(empno,deptno)

            *
ERROR at line 1:
ORA-20501: Department must exist in EMP table.

There's a million ways to do this, probably many of them more elegant than this, but there you go.

Best wishes,

Steve

In article <92050g$j38$1_at_nnrp1.deja.com>,   seanldus_at_my-deja.com wrote:
> Hi Dave,
>
> Thanks for the answer. That was sort of a sanity check. If you have
 a
> field in one table which cannot be uniqe, but has to be there in order
> to exist in another table, I have to use a trigger? Maybe you have
 run
> in to this before?
>
> In article <91u7uc$7jl$1_at_news3.cadvision.com>,
> "Dave Haas" <davehaas_at_hotmail.com> wrote:
> > Sean. A foreign key REQUIRES either a unique or primary key to
 point
 to.
> >
> > Regards,
> >
> > Dave Haas
> >
> > <seanldus_at_my-deja.com> wrote in message news:91u5s6
 $35r$1_at_nnrp1.deja.com...
> > > Am I missing something? Can I create a foreign key that
 references
 a
> > > column that is not a primary or unique key?
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 22 2000 - 12:41:17 CST