RE: Foreign Key Constraint ...

  • From: "Karsten Weikop" <oraclel@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Jan 2005 09:06:13 +0100

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes
Pacheco
Sent: Friday, January 07, 2005 12:10 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
Date: 01/06/05 19:02:38
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l

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

Other related posts: