On Wed, 6 Aug 2003 11:47:35 +0200, "hobbes" <pasdespam@yahoo.fr>
wrote:

>Hi !
>
>since my last post was answered successfully , i try antoher one
>
>I want to know Index(es) and primary keys on specific tables.
>
>
>So i make a query between systables and sysindices.

This will give you the index name. Remember that if the unique index
doesn't exist when you generate the primary key constraint, Informix
will create one for you, as a combination of tabid and constrid. When
Informix does this for you, the first character if idxname will be
blank.

>
>But as i want to know if it is a primary key => I check the value of
>sysconstraints.constrtype.

Constrtype should be 'P'.

>When i try to make the link between => sysconstrints and sysindexes on
>idxname it does not work.
>
>The explanation:
>=============
>in fact idxname in sysconstraints is not always filled with the index name.
>sometimes it is fill with a value and sometimes not.(just blank)
>

On my version, when idxname isn't set, it is because constrtype is
'N', a not null constraint on a column. What is your Informix
version?

>for example with 2 tables:
>customer
>dealer
>
>they each have 1 PK and 1 IDX.
>
>The informations in sysconstraints are here , but
>=> for the table customer => i 've got the idxname filled. (so i can make
>difference between the idx and the PK)
>=> for the table customer => idxname not filled.....
>
>the tables have been created at the same time in the same script (with 250
>other tables which some have the sysconstraint.idxname information and other
>not)
>so, my question is ... why ?
>
>In fact is there a way to regenerate the idxname or am i doing somethig
>wrong ?

dbschema should allow you to see the index name, unless it is a
system-generated index name.
>
>Thank you by advance for any answers !
>
>Arnaud
>

> This will give you the index name. Remember that if the unique index
> doesn't exist when you generate the primary key constraint, Informix
> will create one for you, as a combination of tabid and constrid. When
> Informix does this for you, the first character if idxname will be
> blank.

Yes that what i'm trying to get ...
in fact i want to obtain 3 things :
1)the index name,
2)the uniquess of the index
3)if the index is a primary key or not

> >When i try to make the link between => sysconstrints and sysindexes on
> >idxname it does not work.
> >
> >The explanation:
> >=============
> >in fact idxname in sysconstraints is not always filled with the index
name.
> >sometimes it is fill with a value and sometimes not.(just blank)
> >
>
> On my version, when idxname isn't set, it is because constrtype is
> 'N', a not null constraint on a column. What is your Informix
> version?

I use IDS 9.3 (i wait for the 9.4 ..... IBM seems on hollyday ...)
Something frightened me in your answer => 99% of our database is made with
not null constraint on columns.......
(i gonna try to find information about that)


Ok, thk you for the answer....

=======
=======

AND FINALLY .....
after checking my database creation script ....
i noticed one big (big, really ..... i can say huge) BUG.
My script is auto-generated from an Oracle database .....
and the generation of the primary key script is done randomly due to a
"poor" parameter...


so this time too , it's my fault.
I'm sorry....
thk you for the answer, it put me on the right way to find the issue.


Bye
Arnaud

Hope that the not null constraints isn't a limitation !

On Wed, 6 Aug 2003 16:54:00 +0200, "hobbes" <pasdespam@yahoo.fr>
wrote:

>> This will give you the index name. Remember that if the unique index
>> doesn't exist when you generate the primary key constraint, Informix
>> will create one for you, as a combination of tabid and constrid. When
>> Informix does this for you, the first character if idxname will be
>> blank.
>
>Yes that what i'm trying to get ...
>in fact i want to obtain 3 things :
>1)the index name,
>2)the uniquess of the index
>3)if the index is a primary key or not


OK, I see now . . .

Join between sysindexes and sysconstraints would be tabid and idxname.
You could add constrtype = 'P' also

>
>> >When i try to make the link between => sysconstrints and sysindexes on
>> >idxname it does not work.
>> >
>> >The explanation:
>> >=============
>> >in fact idxname in sysconstraints is not always filled with the index
>name.
>> >sometimes it is fill with a value and sometimes not.(just blank)
>> >
>>
>> On my version, when idxname isn't set, it is because constrtype is
>> 'N', a not null constraint on a column. What is your Informix
>> version?
>
>I use IDS 9.3 (i wait for the 9.4 ..... IBM seems on hollyday ...)
>Something frightened me in your answer => 99% of our database is made with
>not null constraint on columns.......
>(i gonna try to find information about that)
>

Sounds like Lawson . . . . 8-)

You could isolate constraint type by using "constrtype = 'P'" as part
of your join. Shouldn't make any difference, since null constraints
do not have an associated index.
>
>Ok, thk you for the answer....
>
>=======
>=======
>
>AND FINALLY .....
>after checking my database creation script ....
>i noticed one big (big, really ..... i can say huge) BUG.
>My script is auto-generated from an Oracle database .....
>and the generation of the primary key script is done randomly due to a
>"poor" parameter...
>

Thus, the PKs were never created? That might cause a problem on the
Informix side if you're looking for primary keys that don't exist.
Informix is good, but not that good . . . .8-)

>
>so this time too , it's my fault.
>I'm sorry....
>thk you for the answer, it put me on the right way to find the issue.
>
>
>Bye
>Arnaud
>
>Hope that the not null constraints isn't a limitation !
>

On Wed, 6 Aug 2003 18:48:19 +0200, "hobbes" <pasdespam@yahoo.fr>
wrote:

>> Join between sysindexes and sysconstraints would be tabid and idxname.
>> You could add constrtype = 'P' also
>
>That's ok... that's what i made and know all works fine.
>
>> >I use IDS 9.3 (i wait for the 9.4 ..... IBM seems on hollyday ...)
>> >Something frightened me in your answer => 99% of our database is made
>with
>> >not null constraint on columns.......
>> >(i gonna try to find information about that)
>> >
>>
>> Sounds like Lawson . . . . 8-)
>
>??
>i'm sure i'm missing something ...

Lawson is an ERP software package. Notorious for wide tables and an
upcoming lack of Informix / Sybase support . . . . (grrrrrrrrrrr)
>
>> You could isolate constraint type by using "constrtype = 'P'" as part
>> of your join. Shouldn't make any difference, since null constraints
>> do not have an associated index.
>
>yes, i think that will do the trick like that...
>
>
>> >AND FINALLY .....
>> >after checking my database creation script ....
>> >i noticed one big (big, really ..... i can say huge) BUG.
>> >My script is auto-generated from an Oracle database .....
>> >and the generation of the primary key script is done randomly due to a
>> >"poor" parameter...
>> >
>>
>> Thus, the PKs were never created? That might cause a problem on the
>> Informix side if you're looking for primary keys that don't exist.
>
>In fact i've got a first query that create a index unique on a column, then
>i "should" have altered it .....
>but some alter command have vanished .... (i got to rewrote our export
>program in a much cleaner way i think)
>
>> Informix is good, but not that good . . . .8-)
>
>huhu
>
>
>but just another question :
>does the index (not unique , not PK)
>are referenced in sysconstraints ?

No, only indices that are concerned with a constraint are listed in
sysconstraints. All indices are listed in sysindexes

>Do we have to see the name of the index in sysconstrainsts ?

Only if it ties to a constraint.

>(in idxname , or constrname)
>just for information, since i do not need this
>
>
>
>Thank you once again
>Arnaud
>

On Wed, 06 Aug 2003 12:48:19 -0400, hobbes wrote:
<SNIP>

> but just another question :
> does the index (not unique , not PK)
> are referenced in sysconstraints ?
> Do we have to see the name of the index in sysconstrainsts ? (in idxname
> , or constrname)
> just for information, since i do not need this

All constraints which require an index (ie PK, FK, UNIQUE) will have the
index entered in the syscontraints, yes. Some constraints (Check, NOT
NULL) do not require an index and so the field is NULL.

Art S. Kagel