RE: Foreign Key Constraint ...
- From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 07 Jan 2005 10:54:32 +0100
Hi,
Karsten already mentioned, the problem with the trigger
solution. Let me give an example of a test that could
bring a trigger solution to fail in a multi user
environment.
Session 1)
Delete from a where x1 =3D 502;
Trigger fires - OK there are no chrildren.
Session 2)
Insert into b values (502, ...);
Trigger fires, and everything is OK, because session 2
can see the row - the deletion in session 1 is not
committed yet.
commit;
Session 1)
=20
commit;
Sad, Sad. Now our database is in an inconsistent state.
I think, that I read it in "Oracle Insights", but I
do not remember, who I shoud give credit for it.
Regards
Jesper Haure Norrevang
-----Oprindelig meddelelse-----
Fra: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=E5
vegne af Karsten Weikop
Sendt: 7. januar 2005 09:06
Til: oracle-l@xxxxxxxxxxxxx
Cc: jreyes@xxxxxxxxxxxxxxxx; ltiu@xxxxxxxxxxxxx
Emne: 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.=20
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));=20
create table b (x1 number, x1_shadow number);=20
ALTER TABLE B ADD (CONSTRAINT B_FK FOREIGN KEY (X1_shadow) REFERENCES A
(X1));=20
CREATE OR REPLACE TRIGGER b_bi=20
BEFORE INSERT or update ON B=20
FOR EACH ROW=20
BEGIN=20
if :new.x1 >=3D 1000 then=20
:new.x1_shadow :=3D :new.x1;=20
end if;=20
END;=20
/=20
prompt Seed primary table
insert into a(x1) values ( 900);=20
insert into a(x1) values (1100);=20
prompt Insert into child table, OK as rows in primary table exists
insert into b (x1) values ( 900);=20
insert into b (x1) values (1100);=20
prompt Insert into child table, OK even if primary key does not exists a
value is below 1000
insert into b (x1) values ( 901);=20
prompt Insert into child table, FAILS as value is > 1000 and primary key
does not exist
insert into b (x1) values (1101);=20
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=20
=20
Juan Carlos Reyes Pacheco
=20
=20
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
=20
-------Original Message-------
=20
From: ltiu@xxxxxxxxxxxxx
Date: 01/06/05 19:02:38
To: oracle-l@xxxxxxxxxxxxx
Subject: Foreign Key Constraint ...
=20
Hello,
=20
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?
=20
Thanks.
=20
--
Lyndon Tiu
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
