Whats Informix thinking or Idea
Ian asked why indexes implicitly created by a PRIMARY KEY clause are "hidden" (names beginning with a space) and therefore don't appear in dbschema output, making schemas hard to reverse-engineer. Replies explained this is by design: the index is an implementation detail owned by the constraint, you can't drop it directly, and the space-prefixed name avoids collisions with user-created index names. The constraint itself does appear in dbschema, so recreating the table recreates the index (under a different name). Art Kagel also offered his myschema utility (in utils2_ak on the IIUG repository) as a dbschema replacement that outputs such indexes with usable names.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi;
What are the reasons for hiding indexes created from the "primary
key(...)" fragment of table creation?
I now have a very long established Informix system updated by recent
application upgrades that are no longer "reverse engineer able" via
`dbschema`
These Indexes seem to have a name starting with the space character !
Boy - I am getting too ole for this !!
Regards
Ian
On Sep 4, 8:55 am, ian <ipel...@yahoo.com> wrote:
> Hi;
>
> What are the reasons for hiding indexes created from the "primary
> key(...)" fragment of table creation?
>
> I now have a very long established Informix system updated by recent
> application upgrades that are no longer "reverse engineer able" via
> `dbschema`
>
> These Indexes seem to have a name starting with the space character !
>
> Boy - I am getting too ole for this !!
>
> Regards
> Ian
Get my dbschema replacement utility, myschema, which will output the
hidden indexes with usable names. This is one major reason I still
maintain the beast.
Myschema, a replacement for dbschema that implements all dbschema
features except -hd with significant extensions, is included in the
package utils2_ak which can be downloaded from the IIUG Software
Repository.
Art S. Kagel
ian wrote:
> Hi;
>
> What are the reasons for hiding indexes created from the "primary
> key(...)" fragment of table creation?
>
> I now have a very long established Informix system updated by recent
> application upgrades that are no longer "reverse engineer able" via
> `dbschema`
>
> These Indexes seem to have a name starting with the space character !
>
> Boy - I am getting too ole for this !!
>
> Regards
> Ian
>
It has always been like that...
You didn't create the index explicitly so it does not appear in dbschema.
But the "primary key" will, so, if you use it to recreate the table the index
will be there, just with another name...
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On 4 Sep, 19:40, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Sep 4, 8:55 am, ian <ipel...@yahoo.com> wrote:
>
> > Hi;
>
> > What are the reasons for hiding indexes created from the "primary
> > key(...)" fragment of table creation?
>
> > I now have a very long established Informix system updated by recent
> > application upgrades that are no longer "reverse engineer able" via
> > `dbschema`
>
> > These Indexes seem to have a name starting with the space character !
>
> > Boy - I am getting too ole for this !!
>
> > Regards
> > Ian
>
> Get my dbschema replacement utility, myschema, which will output the
> hidden indexes with usable names. This is one major reason I still
> maintain the beast.
>
> Myschema, a replacement for dbschema that implements all dbschema
> features except -hd with significant extensions, is included in the
> package utils2_ak which can be downloaded from the IIUG Software
> Repository.
>
> Art S. Kagel
Thanks Art,
But I was really after the reasoning Informix or whoever used in
hiding these indexes?
Ian
> Thanks Art, > But I was really after the reasoning Informix or whoever used in > hiding these indexes? > Ian The reason may be that you do not try and drop the index but the constraint if you need to get rid of the index/constraint. Dropping will not work anyway. Superboer.
ian wrote:
> But I was really after the reasoning Informix or whoever used in
> hiding these indexes?
I don't _know_ but I suspect it that it's because of a mismatch between
SQL and the way Informix works underneath. In implementation terms
primary keys require a unique index but standard SQL allows you to do
things like:
create table foo (
bar varchar(100),
primary key (bar)
);
I guess the choice for the developers was either not to allow the
standard SQL above and force explicit creation of a unique index before
a primary key could be created, or allow the SQL above and create an
index implicitly. Why then it's hidden is probably a design choice but I
suspect people like to see the same SQL they used to create the table
outputted by 'dbschema'.
Ben.
On Sep 5, 4:33 am, ian <ipel...@yahoo.com> wrote:
> On 4 Sep, 19:40, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
>
>
> > On Sep 4, 8:55 am, ian <ipel...@yahoo.com> wrote:
>
> > > Hi;
>
> > > What are the reasons for hiding indexes created from the "primary
> > > key(...)" fragment of table creation?
>
> > > I now have a very long established Informix system updated by recent
> > > application upgrades that are no longer "reverse engineer able" via
> > > `dbschema`
>
> > > These Indexes seem to have a name starting with the space character !
>
> > > Boy - I am getting too ole for this !!
>
> > > Regards
> > > Ian
>
> > Get my dbschema replacement utility, myschema, which will output the
> > hidden indexes with usable names. This is one major reason I still
> > maintain the beast.
>
> > Myschema, a replacement for dbschema that implements all dbschema
> > features except -hd with significant extensions, is included in the
> > package utils2_ak which can be downloaded from the IIUG Software
> > Repository.
>
> > Art S. Kagel
>
> Thanks Art,
> But I was really after the reasoning Informix or whoever used in
> hiding these indexes?
> Ian
Fernando hit the nail most closely. You didn't create the index so
you cannot drop the index. You only created the constraint that owns
the index, so that's the only object you need to know about.
It's an implementation detail that IDS uses a unique index to
implement unique and primary key constraints so the index is not
something you 'need' access to. Indeed, if you create the index
independently then create the constraint - which will use the existing
index - and later drop the independent index keeping the constraint in
place the engine will simply rename the index to a hidden name, so you
can think you dropped it but the engine still has the actual index in
place to support the constraint. You can select the sysconstraints
and sysindexes/sysindices records before and after the index drop to
see the change most dramatically.
Art S. Kagel
ian wrote:
> On 4 Sep, 19:40, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>> On Sep 4, 8:55 am, ian <ipel...@yahoo.com> wrote:
> Thanks Art,
> But I was really after the reasoning Informix or whoever used in
> hiding these indexes?
> Ian
>
Simple reason. To avoid index name collision with the user created
indexes. We aren't really hiding them. If you check sysindexes and
sysindices, you will see that they are there. However since we have to
create the indexes as a part of creating the primary key constraint and
not as part of a create index statement, we have to ensure that the
index has a unique name and that the name can not be used by the
customer to create an index via the create index statement.
These indexes do not appear within the dbschema output because they are
not created via the create index statement.