foreign keys

foreign keys

Post by joe » Sun, 31 Dec 1899 09:00:00



I'm in the process of restructuring a database that uses triggers to enforce
referential integrity instead of foreign keys.
If I were to drop the triggers and use foreign keys(fully indexed), what
performance gains, if any, would I see? I guess I'm trying to decide
whether it's worth the effort.
Thanks
 
 
 

foreign keys

Post by karsten reinck » Sun, 31 Dec 1899 09:00:00



> I'm in the process of restructuring a database that uses triggers to enforce
> referential integrity instead of foreign keys.
> If I were to drop the triggers and use foreign keys(fully indexed), what
> performance gains, if any, would I see? I guess I'm trying to decide
> whether it's worth the effort.
> Thanks

do you wan't an open answer? ok:

(1) to use a relational database without any foreign key is to use an
unrelational database. and that is not state of the art.
(2) do you mean that any trigger can do the work in any sense better
than the oracle-rdbs created by a team which has have to think about
such fundamental tasks?

--
       salut d'Allemagne kr
-----------------------------------
les chats cont les vrais hdonistes
et devraient tre donc notre idal.
-----------------------------------
 karsten reincke [ 0541/9696223 ]


 
 
 

1. Trigger to check a foreign key without using foreign key

Hello,

I want to use a trigger to check a foreign key.

Let's assume :

table LIGNES_V contains field ID_ARTICLE char(25)
table STOCK contains field S_ID char(10)

In table LIGNES_V, ID_ARTICLE must contain '' or a valid value from S_ID in
STOCK
(because of the '' -> could not use a standard foreign key)

In the update trigger of LIGNES_V, i placed

if update(id_article) and exists (select id_article from inserted where
(id_article>' ') and  (left(id_article,10) not in (select s_id from stock)))
 RAISERROR ( 'ID_ARTICLE does not match S_ID !',16,1 )

it works very fine and gives the correct result.

BUT : i assume that this request containing a sub-request will result in bad
performances.

Does somebody have an idea to write this trigger on a more-performing way ?

Thanks for help,

Kind regards,

Guy

2. MSDTC Service

3. Oracle JVM problems

4. foreign key w/o foreign/primary key

5. Inconsistent Select

6. Foreign Key as subset of Primary Key

7. TRUNCATE and ARCHIVELOG mode

8. Identification of a primary key/foreign key

9. One FOREIGN KEY refrences a Table with two Primary Keys

10. Copying Primary Key to Foreign Key

11. Changing Primary Key Values when foreign key constraints exist

12. delete primary key records together with the foreign keys records