Problem with contraints - this one will hurt your brain!

[jdbolt]

Here is the problem; I have a self-referencing table:

modifier (primary key)
parentModifier (foreign key references modifier)

I need a way to prevent a recursive relationship, that is a modifier's parent cannot be any of its children. I know a way to get all the children of a modifier:

select
modifier,
level
from modifier
connect by prior modifier = parentModifier
start with modifier = 'BBCONN'
order by level

So know, the pseudo code would be:

UPDATE modifierTable SET parentModifier = 'BBCI' where modifier = 'BBCONN'

if new parent modifier is in
(
select
modifier,
level
from modifier
connect by prior modifier = parentModifier
start with modifier = 'BBCONN'
order by level)
}
then don't add, else ok to add.

So in ORacle I would need something like:

ALTER TABLE modifier
add CONSTRAINT check_modifierParent
CHECK
(parentModifier IN
(
select
modifier,
from modifier
connect by prior modifier = parentModifier
start with modifier = 'BBCI'
order by level
)
)

But I can't use subqueries in contraints, any way to get around this? Use a trigger perhaps?

 

[sem]

Yes, you should use a trigger. But be carefull, querying triggered table is not quite simple task in Oracle
A hint: read about mutating

Regards, Dima

http://seminihin.narod.ru