What this guy mean to say..
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 > > Make Sense? > > Please let me know if you have any more questions. > > Thanks, > > Sanjeev K. Sagar > > Susan LeRoy wrote: > > > Sanjeev - > > Do you have an answer for this question? > > Susan > > > > ------------------------------------------------------- > > > > Subject: Informix internal indexes > > Date: Wed, 07 Jun 2000 11:10:45 -0500 > > From: Mike Sotzen <mike.sotzen@sabre.com> > > Organization: Sabre Inc. > > To: Susan Leroy <susan.leroy@sabre.com> > > > > I have a question for you on Informix referential constraints. Why is > > it that > > Informix automatically creates an internal index on a referential > > constrained column or columns? Is there any way around this? > > > > Here's why I don't always want them: > > > > Parent Table > > ========= > > ColA PK > > ColB PK > > > > Child Table > > ======== > > ColA PK > > ColB PK > > ColC PK > > > > I have a Primary Key on ColA, ColB in the Parent Table. I have a > > Primary Key on ColA, ColB, ColC on the Child Table. I create a > > constraint between the Parent and Child table, Informix automatically > > creates an index on ColA, ColB on the Child table. This index is > > redundant because the first two columns of the Primary Key index could > > be used for the constraint. Am I missing something here? This requires > > > > me to have 100 extra indexes on my 134 table database. > > > > I would really appreciate any input you might have on this. > > > > Thanks. > > > > ------------------------------------------------------- > > > > Mike Sotzen <mike.sotzen@sabre.com> > > Sr. Consultant > > Sabre > > BTS Development > > > > Mike Sotzen > > Sr. Consultant <mike.sotzen@sabre.com> > > Sabre > > BTS Development > > 1 East Kirkwood Blvd. MD 7560 Fax: 817-264-8171 > > Southlake Work: 817-264-2353 > > TX > > 76092 > > USA > > Additional Information: > > Last Name Sotzen > > First Name Mike > > Version 2.1 Mike Sotzen Sr. Consultant Sabre BTS Development <mike.sotzen@sabre.com> 1 East Kirkwood Blvd. MD 7560 Southlake TX 76092 USA Fax: 817-264-8171 Work: 817-264-2353 Additio
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 > > > > Make Sense? > > > > Please let me know if you have any more questions. > > > > Thanks, > > > > Sanjeev K. Sagar > > > > Susan LeRoy wrote: > > > > > Sanjeev - > > > Do you have an answer for this question? > > > Susan > > > > > > > ------------------------------------------------------- > > > > > > > Subject: Informix internal indexes > > > Date: Wed, 07 Jun 2000 11:10:45 -0500 > > > From: Mike Sotzen <mike.sotzen@sabre.com> > > > Organization: Sabre Inc. > > > To: Susan Leroy <susan.leroy@sabre.com> > > > > > > I have a question for you on Informix referential > constraints. Why is > > > it that > > > Informix automatically creates an internal index on > a referential > > > constrained column or columns? Is there any way > around this? > > > > > > Here's why I don't always want them: > > > > > > Parent Table > > > ========= > > > ColA PK > > > ColB PK > > > > > > Child Table > > > ======== > > > ColA PK > > > ColB PK > > > ColC PK > > > > > > I have a Primary Key on ColA, ColB in the Parent > Table. I have a > > > Primary Key on ColA, ColB, ColC on the Child > Table. I create a > > > constraint between the Parent and Child table, > Informix automatically > > > creates an index on ColA, ColB on the Child table. > This index is > > > redundant because the first two columns of the > Primary Key index could > > > be used for the constraint. Am I missing something > here? This requires > > > > > > me to have 100 extra indexes on my 134 table > database. > > > > > > I would really appreciate any input you might have > on this. > > > > > > Thanks. > > > > > > > ------------------------------------------------------- > > > > > > > Mike Sotzen <mike.sotzen@sabre.com> > > > Sr. Consultant > > > Sabre > > > BTS Development > > > > > > Mike Sotzen > > > Sr. Consultant > <mike.sotzen@sabre.com> > > > Sabre > > > BTS Development > > > 1 East Kirkwood Blvd. MD 7560 Fax: 817-264-8171 > > > > Southlake Work: > 817-264-2353 > > > TX > > > 76092 > > > USA > > > Additional Information: > > > Last Name Sotzen > > > First Name Mike > > > Version 2.1 > > > > Mike Sotzen > Sr. Consultant > Sabre > BTS Development > <mike.sotzen@sabre.com> > 1 East Kirkwood Blvd. MD 7560 > Southlake > TX > 76092 > USA > Fax: 817-264-8171 > Work: 817-264-2353 > > Additio >
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 wherea.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
> > >
> > > Make Sense?
> > >
> > > Please let me know if you have any more questions.
> > >
> > > Thanks,
> > >
> > > Sanjeev K. Sagar
> > >
> > > Susan LeRoy wrote:
> > >
> > > > Sanjeev -
> > > > Do you have an answer for this question?
> > > > Susan
> > > >
> > > >
> > -------------------------------------------------------
> >
> > > >
> > > > Subject: Informix internal indexes
> > > > Date: Wed, 07 Jun 2000 11:10:45 -0500
> > > > From: Mike Sotzen <mike.sotzen@sabre.com>
> > > > Organization: Sabre Inc.
> > > > To: Susan Leroy <susan.leroy@sabre.com>
> > > >
> > > > I have a question for you on Informix referential
> > constraints. Why is
> > > > it that
> > > > Informix automatically creates an internal index on
> > a referential
> > > > constrained column or columns? Is there any way
> > around this?
> > > >
> > > > Here's why I don't always want them:
> > > >
> > > > Parent Table
> > > > =========
> > > > ColA PK
> > > > ColB PK
> > > >
> > > > Child Table
> > > > ========
> > > > ColA PK
> > > > ColB PK
> > > > ColC PK
> > > >
> > > > I have a Primary Key on ColA, ColB in the Parent
> > Table. I have a
> > > > Primary Key on ColA, ColB, ColC on the Child
> > Table. I create a
> > > > constraint between the Parent and Child table,
> > Informix automatically
> > > > creates an index on ColA, ColB on the Child table.
> > This index is
> > > > redundant because the first two columns of the
> > Primary Key index could
> > > > be used for the constraint. Am I missing something
> > here? This requires
> > > >
> > > > me to have 100 extra indexes on my 134 table
> > database.
> > > >
> > > > I would really appreciate any input you might have
> > on this.
> > > >
> > > > Thanks.
> > > >
> > > >
> > -------------------------------------------------------
> >
> > > >
> > > > Mike Sotzen <mike.sotzen@sabre.com>
> > > > Sr. Consultant
> > > > Sabre
> > > > BTS Development
> > > >
> > > > Mike Sotzen
> > > > Sr. Consultant
> > <mike.sotzen@sabre.com>
> > > > Sabre
> > > > BTS Development
> > > > 1 East Kirkwood Blvd. MD 7560 Fax: 817-264-8171
> >
> > > > Southlake Work:
> > 817-264-2353
> > > > TX
> > > > 7
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, sysconstraintsb
> 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
> > > >
> > > > Make Sense?
> > > >
> > > > Please let me know if you have any more questions.
> > > >
> > > > Thanks,
> > > >
> > > > Sanjeev K. Sagar
> > > >
> > > > Susan LeRoy wrote:
> > > >
> > > > > Sanjeev -
> > > > > Do you have an answer for this question?
> > > > > Susan
> > > > >
> > > > >
> > > -------------------------------------------------------
> > >
> > > > >
> > > > > Subject: Informix internal indexes
> > > > > Date: Wed, 07 Jun 2000 11:10:45 -0500
> > > > > From: Mike Sotzen <mike.sotzen@sabre.com>
> > > > > Organization: Sabre Inc.
> > > > > To: Susan Leroy <susan.leroy@sabre.com>
> > > > >
> > > > > I have a question for you on Informix referential
> > > constraints. Why is
> > > > > it that
> > > > > Informix automatically creates an internal index on
> > > a referential
> > > > > constrained column or columns?
No no. Try this one:
CREATE TABLE accounts (
acc_num INTEGER,
acc_type INTEGER,acc_descr CHAR(20) );
create unique index PK_accounts on accounts(acc_num, acc_type);
ALTER TABLE accounts ADD CONSTRAINT primary key (acc_num, acc_type);
CREATE TABLE sub_accounts (
sub_acc INTEGER,
ref_num INTEGER,
ref_type INTEGER,sub_descr CHAR(20));
create unique index PK_sub_accounts on sub_accounts(
sub_acc,
ref_num,
ref_type);
create index FK_sub_accounts on sub_accounts ( ref_num, ref_type );
ALTER TABLE sub_account add constaint
PRIMARY KEY (sub_acc, ref_num, ref_type));
alter table sub_accounts add constraint FOREIGN KEY(ref_num, ref_type)
REFERENCES accounts;
Then you will see the constraints will use these three indexes as
advertised.
Art S. Kagel
Sanjeev sagar wrote:
>
> 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
> > > >
> > > > Make Sense?
> > > >
> > > > Please let me know if you have any more questions.
> > > >
> > > > Thanks,
> > > >
> > > > Sanjeev K. Sagar
> > > >
> > > > Susan LeRoy wrote:
> > > >
> > > > > Sanjeev -
> > > > > Do you have an answer for this question?
> > > > > Susan
> > > > >
> > > > >
> > > -------------------------------------------------------
> > >
> > > > >
> > > > > Subject: Informix internal indexes
> > > > > Date: Wed, 07 Jun 2000 11:10:45 -0500
> > > > > From: Mike Sotzen <mike.sotzen@sabre.com>
> > > > > Organization: Sabre Inc.
> > > > > To: Susan Leroy <susan.leroy@sabre.com>
> > > > >
> > > > > I have a question for you on Informix referential
> > > constraints. Why is
> > > > > it that
> > > > > Informix automatically creates an internal index on
> > > a referential
> > > > > constrained column or columns? Is there any way
> > > around this?
> > > > >
> > > > > Here's why I don't always want them:
> > > > >
> > > > > Parent Table
> > > > > =========
> > > > > ColA PK
> > > > > ColB PK
> > > > >
> > > > > Child Table
> > > > >