IDS 7.30: can this be done with a constraint?

Let's say I have a table foo... create table foo ( handle varchar(40,12) primary key, parent varchar(40,12) references foo(handle) constraint foo_handle, start decimal(10) not null constraint foo_start_notnull, end decimal(10) not null constraint foo_end_notnull, check (start <= end) constraint startlessthanend ); A few "top level" foos have their parent field set to NULL, but all other foos have parents, in a hierarchical relationship. I'd like to ensure that every child foo is a subrange of its parent. That is, its start-end range must lie within the start-end range of its parent. The obvious simpleminded way to do this is not allowed: > alter table foo add constraint > (check (start >= (select start from foo p where p.handle = parent)) > constraint startwithinparent); 677: Check constraint cannot contain subqueries or procedures. (Of course I'd want to have a similar constraint for end as well) Is there any way to enforce what I'm trying to enforce using constraints? If it can't be done using constraints, what other tool is available to enforce this restriction, if any? -- Cos (Ofer Inbar) -- cos@polyamory.org http://www.leftbank.com/CosWeb/ -- WBRS (100.1 FM) -- info@wbrs.org http://www.wbrs.org/ A cos is an abstraction for a stream or datagram channel, used in BSD and BSD derivatives. -- Ben Tober <tober@wizvax.methuen.ma.us>

You can try an insert trigger - procedure combination. Something like this : CREATE PROCEDURE startlessthanend( l_parent LIKE foo.parent, l_start LIKE foo.start, l_end LIKE foo.end)DEFINE error_message VARCHAR(255); DEFINE l_count SMALLINT; SELECT COUNT(*) INTO l_count FROM foo WHERE handle = l_parent AND (start > l_start OR end < l_end); IF l_count > 0 THEN LET error_message="Ouch!"; -- or something more meaningful RAISE EXCEPTION -746, 0, error_message; END IF; END PROCEDURE; create trigger t_ins_foo insert on foo referencing new as postfor each row when (post.parent is not null ) (execute procedure startlessthanend(post.parent, post.start, post.end)); Rudy Ofer Inbar wrote: > Let's say I have a table foo... > > create table foo > ( handle varchar(40,12) primary key, > parent varchar(40,12) references foo(handle) constraint foo_handle, > start decimal(10) not null constraint foo_start_notnull, > end decimal(10) not null constraint foo_end_notnull, > check (start <= end) constraint startlessthanend > );> > A few "top level" foos have their parent field set to NULL, but all > other foos have parents, in a hierarchical relationship. I'd like to > ensure that every child foo is a subrange of its parent. That is, its > start-end range must lie within the start-end range of its parent. > > The obvious simpleminded way to do this is not allowed: > > alter table foo add constraint > > (check (start >= (select start from foo p where p.handle = parent)) > > constraint startwithinparent);> > 677: Check constraint cannot contain subqueries or procedures.> > (Of course I'd want to have a similar constraint for end as well) > > Is there any way to enforce what I'm trying to enforce using > constraints? If it can't be done using constraints, what other tool > is available to enforce this restriction, if any? > > -- Cos (Ofer Inbar) -- cos@polyamory.org http://www.leftbank.com/CosWeb/ > -- WBRS (100.1 FM) -- info@wbrs.org http://www.wbrs.org/ > A cos is an abstraction for a stream or datagram channel, used in BSD > and BSD derivatives. -- Ben Tober <tober@wizvax.methuen.ma.us>