Re: Implementing complicated constraints

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 29 Sep 2004 14:01:58 +0200
Message-ID: <cje87o$n2i$1@news.BelWue.DE>

Tony Andrews wrote:
>
>
> Standard SQL has a syntax to support such multi-tabel constraints, but
> Oracle does not support it, so you have to use some kind of work
> around. I suggested one using a materialized view earlier, what do you
> feel about that? It is "declarative", and it doesn't involve changing
> your table design.
>

Another option you could try is a unique function based index (Tom Kyte gives an example in his book 'Effective Oracle by Design' where I got the idea from):

baer_at_DEMO10G>create table c (cid integer primary key);

Table created.

baer_at_DEMO10G>create table b (bid integer primary key, cid integer references c(cid));

Table created.

baer_at_DEMO10G>create table a (aid integer primary key, bid integer references b(bid), atxt varchar2(10));

baer_at_DEMO10G>create or replace function fn_get_cid_for_bid(p_bid integer) return integer 2 deterministic
3 is
4 l_cid integer;
5 begin

6      select c.cid into l_cid from c,b where b.cid=c.cid and b.bid=p_bid;
7      return l_cid;

8 end;
9 /

Function created.

baer_at_DEMO10G> create unique index idx_fn_bid on a (atxt, fn_get_cid_for_bid(bid));

Index created.

baer_at_DEMO10G> begin
2 for x in 1..10 loop
3 insert into c values (x);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

baer_at_DEMO10G> begin
2 for x in 1..10 loop
3 insert into b values (x, decode (mod(x,3), 0, 4, mod(x,3))); 4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

baer_at_DEMO10G>insert into a values (1,1,'1'); 1 row created.

baer_at_DEMO10G>insert into a values (2,4,'1'); insert into a values (2,4,'1')
*
ERROR at line 1:
ORA-00001: unique constraint (BAER.IDX_FN_BID) violated

HTH Holger Received on Wed Sep 29 2004 - 07:01:58 CDT