Help w/indexs&constraints

Hello... I have 2-tables, R(a,b) and S(a,b). I want to do the following: alter table r add constraint primary key (a) alter table s add constraint (foreign key (a) to r on cascade delete);(the syntax might not be exactly right, but you get the idea) Now...if I do the above, all's well. But...I want the index in a different place. So..if I add: create index pi on p(a) in xxx; ...then no combination of the above will work. In short, I want to do the 2-constraints, but I want the index to be in a specific tablespace, not the default (e.g. create table r(a primary key, ...) Any suggestions on how I can do all of these 3-things successfully and in what order? Thanks in advance, Joe

Joe Trubisz wrote: > I have 2-tables, R(a,b) and S(a,b). > I want to do the following: > alter table r add constraint primary key (a) > alter table s add constraint > (foreign key (a) to r on cascade delete);> (the syntax might not be exactly right, but > you get the idea) > > Now...if I do the above, all's well. > But...I want the index in a different place. > So..if I add: > create index pi on p(a) in xxx;> > ...then no combination of the above will work. > In short, I want to do the 2-constraints, but > I want the index to be in a specific tablespace, > not the default (e.g. create table r(a primary key, ...) > > Any suggestions on how I can do all of these > 3-things successfully and in what order? I've not checked this, nor ever needed to do it, but... Create the two indexes in table spaces as required. Then add the primary key. Then add the foreign key. I think, but am not certain, that the constraints will use the pre-existing indexes when the index exactly matches what the constraint would create in all but name. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>