Re: Subquery in constraints...

"Vadim Tropashko" <vadimtro_at_yho.cm> wrote in message news:mxBad.44$UF1.123_at_news.oracle.com...
> Mikito Harakiri wrote:
> > Suppose you implement foreign key constraint as a subquery in the check
> > constraint clause. Would the DBMS be smart enough to leverage existing
index
> > on the parent_id column?
>
> What is so difficult about it? Checking if parent record exist is just a
> [recursive] query. SQL optimiser must be perfectly able to decide
> whether to use index or not.

Not quite. (Credit answering this goes to Bob Jenkins). What condition would trigger check constraint verification? For example, consider

alter table Detail
check( 0 < (select count(1) from Master where id=master_id) )

Checking constraint when inserting a row into Detail table is indeed easy. However, what do I do if a user deletes a row from Master table? Do I have to check

{} = select 1 from Detail
where not 0 < (select count(1) from Master where id=master_id)

global constraint? It is clearly very expensive until somebody figures out how to check arbitrary global constraint incrementally. Received on Mon Oct 11 2004 - 23:39:59 CEST