RE: Foreign Key Constraint ...

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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] P=E5 vegne af Karsten Weikop
Sendt: 7. januar 2005 09:06
Til: oracle-l_at_freelists.org
Cc: jreyes_at_dazasoftware.com; ltiu_at_alumni.sfu.ca 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_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=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_at_alumni.sfu.ca
Date: 01/06/05 19:02:38
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

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

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