XPost: comp.databases.oracle.server   
   From: mcstockX@Xenquery   
      
   "Agoston Bejo"  wrote in message   
   news:cjbb40$b0d$1@news.caesar.elte.hu...   
   | Hi, I'm having trouble with implementing some constraints on the database   
   | level. An example:   
   |   
   | --Table A(AID, BID ref. B.BID, ATXT)   
   | --Table B(BID, CID ref. C.CID)   
   | --Table C(CID)   
   |   
   | upon insertion into or updating in A I would like to force that ATXT is   
   | unique with respect to CID, i.e.   
   |   
   | SELECT COUNT(*) FROM A,B,C   
   | WHERE A.BID = B.BID   
   | AND B.CID = CID   
   | AND CID = fn_get_cid_for_bid(:new.BID)   
   | AND A.ATXT = :new.ATXT   
   | AND A.AID <> :new.AID   
   |   
   | should be 0.   
   |   
   | This I cannot force with a check constraint since it is not allowed to   
   | contain subqueries.   
   | If I try to write a trigger that checks the above condition and raises an   
   | application error, then I always stumble upon the "table is mutating,   
   | trigger/function may not see it" -type error, since it involves a select   
   on   
   | the table that is being changed at the time.   
   |   
   | However, it would be better to implement such a constraint on the database   
   | level, rather than scatter checks throughout the application.   
   |   
   | Is there a standard way solve this type of problem?   
   |   
   |   
   |   
      
   hopefully i'm understanding the problem correctly....   
      
   there's no standard way, since your uniqueness constraint is based on   
   multiple tables (the way you have it currently designed)   
      
   however, if A.TXT is NOT NULL, then you could denormalize the table   
   implementation and include CID in A and put a unique constraint on (A.CID,   
   A.TXT). if A.TXT is NULL-able, you may want to denormalize the table   
   implementation and have a table that includes just the PK of A (AID?), CID,   
   and TXT, storing actual TXT entries out-of-line from table A, so that a   
   unique constraint can be put on CID and TXT in that table. the reason   
   NULL/NOT NULL comes into play is that oracle would not prevent multiple NULL   
   values in a one-column unique index, but would prevent multiple TXT nulls in   
   this case, since the CID value would be repeated   
      
   ++ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)