RE: Foreign Key Constraint ...

Hi

You can use a trigger, but in multi-user environments, that can cause problems is several persons insert at the same time.

A simple solution could be to add a shadow column on tableB, which only is populated if the column is grater than 1000. A ref. constraint will be made against original column in tableA and shadow column in tableB.

Example:

create table a (x1 number, CONSTRAINT A_PK PRIMARY KEY (X1)); create table b (x1 number, x1_shadow number);

ALTER TABLE B ADD (CONSTRAINT B_FK FOREIGN KEY (X1_shadow) REFERENCES A (X1));

CREATE OR REPLACE TRIGGER b_bi
BEFORE INSERT or update ON B
FOR EACH ROW
BEGIN
  if :new.x1 >= 1000 then
    :new.x1_shadow := :new.x1;
  end if;
END;
/

prompt Seed primary table
insert into a(x1) values ( 900);
insert into a(x1) values (1100);

prompt Insert into child table, OK as rows in primary table exists insert into b (x1) values ( 900);
insert into b (x1) values (1100);

prompt Insert into child table, OK even if primary key does not exists a value is below 1000
insert into b (x1) values ( 901);

prompt Insert into child table, FAILS as value is > 1000 and primary key does not exist
insert into b (x1) values (1101);

Cheers
Karsten Weikop
Make IT, Denmark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Friday, January 07, 2005 12:10 AM
To: oracle-l_at_freelists.org
Subject: Re: Foreign Key Constraint ...

Hi, Use a trigger  

Juan Carlos Reyes Pacheco    

Oracle Certified Professional 9i,10g ( Experience in Oracle Database 7,8i
too)
Developer Certified Professional 6i
8 years of experience in developing, administrating database and designing  

-------Original Message-------
 

From: ltiu_at_alumni.sfu.ca
Date: 01/06/05 19:02:38
To: oracle-l_at_freelists.org
Subject: Foreign Key Constraint ...  

Hello,  

Can we have a foreign key column in tableB that points to a primary key in tableA, but the foreign key is only enforced if the foreign key value is greater than 1000?  

Thanks.  

--

Lyndon Tiu
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 07 2005 - 02:04:40 CST