Re: What this guy mean to say..

I did further experimentation. Informix do not create index only when there is existing index on the whole set of column i.e. in this example if there is a existing index on column 2, 3 then Informix do not create any additional index. The existing index is sharable. If there is only one column or set of columns like only 2 or 2,3,1 Informix will create the index. Thanks to all of you for making this topic clear. Sanjeev K. Sagar Doug Agnew wrote: > Yes, I think you've misinterpreted the 'HOWEVER' > > In your first example, an index was properly created because there was no > index on columns 2 and 3. Granted, there is an index on columns 1, 2 and > 3, but this is significantly different from an index on 2 and 3. The > purpose of the index is to facilitate deletes of the constraining table > (accounts), so the index has to begin (at least) with the foreign key > fields. Whether it must contain ONLY the foreign key fields to qualify > under the 'however' clause, I don't know. > > So, in your first example, Informix is performing EXACTLY as the book > states. > > And your second example also performs correctly, per the book. > > The only thing I don't know is whether or not an index would be built if the > primary key was defined as columns 2, 3 and 1. Then, you would have an > index created on the same columns (ALMOST). I suspect that the index would > be built, but only experimentation would verify. > > HTH, > Doug > > "Sanjeev sagar" <sanjeev.sagar@sabre.com> wrote in message > news:3946591A.AC8BF529@sabre.com... > > As per the Informix Guide, When REFERENTIAL CONSTRAINTS is placed on a > column, > > the database server creates a non-unique index for the columns specified > in the > > referential constraint > > > > However, IF A CONSTRAINT IS ALREADY WAS CREATED ON THE SAME COLUMN OR SET > OF > > COLUMNS, ANOTHE INDEX IS NOT BUILT FOR THE CONSTRAINTS. INSTEAD, THE > EXISTING > > INDEX IS SHARED BY THE CONSTRAINTS. > > > > This is not happening. I have done a small follwoing test > > > > CREATE TABLE accounts ( > > acc_num INTEGER, > > acc_type INTEGER, > > acc_descr CHAR(20), > > PRIMARY KEY (acc_num, acc_type));> > > > CREATE TABLE sub_accounts ( > > sub_acc INTEGER, > > ref_num INTEGER, > > ref_type INTEGER, > > sub_descr CHAR(20), > > PRIMARY KEY (sub_acc, ref_num, ref_type));> > > > >alter table sub_accounts add constraint FOREIGN KEY(ref_num, ref_type) > > REFERENCES accounts; > > >select a.tabname, a.tabid, b.constrid from systables a, sysconstraints b > where> > a.tabid=b.tabid and a.tabid > 99 and > > a.tabname like '%accounts'; > > > > tabname tabid constrid > > > > accounts 123 5 > > sub_accounts 124 6 > > sub_accounts 124 7 > > > > 3 row(s) retrieved. > > > > > select * from sysindexes where tabid=124;> > > > > > > > idxname 124_6 > > owner informix > > tabid 124 > > idxtype U > > clustered > > part1 1 > > part2 2 > > part3 3 > > part4 0 > > part5 0 > > part6 0 > > part7 0 > > part8 0 > > part9 0 > > part10 0 > > part11 0 > > part12 0 > > part13 0 > > part14 0 > > part15 0 > > part16 0 > > levels > > leaves > > nunique > > clust > > > > idxname 124_7 > > owner informix > > tabid 124 > > idxtype D > > clustered > > part1 2 > > part2 3 > > part3 0 > > part4 0 > > part5 0 > > part6 0 > > part7 0 > > part8 0 > > part9 0 > > part10 0 > > part11 0 > > part12 0 > > part13 0 > > part14 0 > > part15 0 > > part16 0 > > levels > > leaves > > nunique > > clust > > > > 2 row(s) retrieved. > > > > DROP TABLE accounts; > > DROP TABLE sub_accounts;> > > > CREATE TABLE accounts ( > > acc_num INTEGER, > > acc_type INTEGER, > > acc_descr CHAR(20), > > PRIMARY KEY (acc_num, acc_type));> > > > CREATE TABLE sub_accounts ( > > sub_acc INTEGER, > > ref_num INTEGER , > > ref_type INTEGER , > > sub_descr CHAR(20), > > FOREIGN KEY (ref_num, ref_type) REFERENCES accounts (acc_num, acc_type));> > > > > select a.tabname, a.tabid, b.constrid from systables a, sysconstraints> b > > where a.tabid=b.tabid and a.tabid > 99 and > > > a.tabname like '%accounts'; > > > > > > tabname tabid constrid > > > > accounts 127 12 > > sub_accounts 128 13 > > > > 2 row(s) retrieved. > > > > > select * from sysindexes where tabid=128;> > > > idxname 128_13 > > owner informix > > tabid 128 > > idxtype D > > clustered > > part1 2 > > part2 3 > > part3 0 > > part4 0 > > part5 0 > > part6 0 > > part7 0 > > part8 0 > > part9 0 > > part10 0 > > part11 0 > > part12 0 > > part13 0 > > part14 0 > > part15 0 > > part16 0 > > levels > > leaves > > nunique > > > > Am I mising anything? > > > > I will appreciate it. > > > > Thanks, > > > > Sanjeev K. Sagar > > > > > > "Clifton M. Bean" wrote: > > > > > An index is created in order to allow the constraint to be quickly > checked. > > > When you want to remove an element from a primary key, Informix will > > > determine if the value is being used before allowing its deletion. > > > > > > Most often, constraints are used within a test or quality assurance > system, > > > to test development code -- nice to get all of those error messages > about > > > value not in field. Once the database had undergone those phases, the > > > foreign key constraints are removed. > > > > > > I have rarely seen the need to maintain foreign key constraints on a > > > production system. > > > > > > Clifton Bean > > > > > > "Sanjeev sagar" <sanjeev.sagar@sabre.com> wrote in message > > > news:3944EDFC.E37D41EA@sabre.com... > > > > No. I don't think you understand. I have contacted > > > > Informix. > > > > > > > > Thanks. > > > > > > > > Sanjeev sagar wrote: > > > > > > > > > Mike, Are you sure that you are talking about > > > > Informix Referential > > > > Integrity > > > > > Constraints. Actually your example does not sound > > > > like that. > > > > > > > > > > Informix support Referential Integrity with the help > > > > of Foreign Key which > > > > > will be Primary Key in the child table. That's the > > > > reason Informix create > > > > > the internal index. As per your example It will be > > > > like > > > > > > > > > > Table A > > > > > --------- > > > > > Col1 PK > > > > > Col2 FK > > > > > > > > > > Table B > > > > > -------- > > > > > Col2 PK > > > > >@