Re: Referencing foreign key

Maoz,

Try using PL/SQL triggers to implement the referencing.....

For the detail table temp2.

CREATE OR REPLACE <trigger>
BEFORE INSERT, UPDATE OF Y,Z
ON temp2 FOR EACH ROW

DECLARE
   dummy NUMBER;

BEGIN
   SELECT 1 INTO dummy
   FROM temp1
   WHERE y = :new.y
   AND z = :new.z;

/* No error */

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ......
   /* Raise an error */

END;
/

For the master table temp1.

CREATE OR REPLACE <trigger>
BEFORE DELETE, UPDATE OF Y,Z
ON temp1 FOR EACH ROW

DECLARE
   dummy NUMBER;

BEGIN
   SELECT 1 INTO dummy
   FROM temp2
   WHERE y = :new.y
   AND z = :new.z;

/* Raise an error */

EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL; END;
/

Maarten

Maoz Mussel <mmussel_at_iil.intel.com> wrote in article <35A3503A.856A8EAE_at_iil.intel.com>...
> Hi there,
>
> I have the following problem/question:
>
> I have table temp1 with the following fields: x,y,z, where (x,y,z) is
> the primary key.
> Also, I have table temp2 with y,z and some other fields.
>
> When trying to create the following CONSTRAINT:
> ALTER TABLE temp2 ADD CONSTRAINT zy_fkey FOREIGN KEY (y,z)
> REFERENCES temp1 (y,z);
>
> I get this error message:
> ORA-02270: no matching unique or primary key for this column-list
>
> Which caused since I don't have (y,z) as unique/promary key on temp1.
> Still, I want to assure that y,z won't be inserted into temp2, unless
> this
> couple exist on temp1 (there could be more than one instance of this
> couple exist on temp1).
>
> Any idea how to do it right ??
>
> TIA,
> Maoz
> mmussel_at_iil.intel.com
>
>
Received on Wed Jul 08 1998 - 13:36:41 CEST