Re: RI question

jvemo@cyberspace.com (Jon C Vemo) wrote: >I have a question. I'm interested in hearing how other people have >addressed this issue (if at all). > >I'm trying to enforce RI through the following means. > >A parent table (TABLE1) has a primary key defined, which is composed of >col1, col2, and col3. The child table (TABLE2) has a foreign key defined on >col1, which references (TABLE1.col1). > >This however does not work, since the primary key on TABLE1 is >(col1,col2,col3). > >How best to enforce RI ?? I could carry TABLE1.col2 and col3 in TABLE2, >then be able to create the foreign key reference, but I really don't need >to carry col2 and col3 in TABLE2. I thought there may be a way to create a >trigger to enforce, but I have not used triggers, so I'm not sure how. > >Anyone have any good ideas? > >TIA >Jon >------------------------------ >Jon C. Vemo >jvemo@cyberspace.com >Bothell, WA U.S.A. I would recommend always using explicit methods for enforcing RI. There are at least 3 good reasons to do so. 1. You don't necessarily want your indexes to go away when RI is removed, which happens when they are implicitly created when you code RI stuff in your create table statements. 2. Implicitly created indexes cannot be altered to cluster. 3. You can put your indexes in different dbspaces. Here is what I do, using two simple tables as examples: create table foo ( f1 int,f2 int ) in dbspace_1 extent size 1000 next size 1000 lock mode row; create unique index foo_idx1 on foo(f1); alter table foo add constraint primary key (f1) constraint foo_const1; create table foo1 ( g1 int,f1 int ) in dbspace_2 extent size 1000 next size 1000 lock mode row; create unique index foo1_idx1 on foo1(g1,f1); create index foo1_idx2 on foo1(f1); alter table foo1 add constraint primary key (g1,f1) constraint foo1_const1; alter table foo1 add constraint foreign key (f1) references foo constraint foo1_const2; There you have it. You have complete control of everything this way. Should you have to remove or play around with RI, you can drop the constraints without losing the indexes. Should you want to create your indexes in another dbspace away from the data, you could do that also. Send me an email if you have more questions. (mreed@radiix.com)