 | |
Re: Index and Pk not referenced into syscontraints !
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||||
| |||||
|
Re: Index and Pk not referenced into syscontraints ! -
08-06-2003
, 09:12 AM
|
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. |
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.
Quote:
|
But as i want to know if it is a primary key => I check the value of sysconstraints.constrtype. |
Quote:
|
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) |
'N', a not null constraint on a column. What is your Informix
version?
Quote:
|
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 ? |
system-generated index name.
Quote:
|
Thank you by advance for any answers ! Arnaud |
#2
| |||
| |||
|
|
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. |
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
Quote:
|
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? |
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 !
#3
| ||||
| ||||
|
|
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
Quote:
|
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) |
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.
Quote:
|
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... |
Informix side if you're looking for primary keys that don't exist.
Informix is good, but not that good . . . .8-)
Quote:
|
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 ! |
#4
| ||||
| ||||
|
|
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 ... |
upcoming lack of Informix / Sybase support . . . . (grrrrrrrrrrr)
Quote:
|
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 exportprogram 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 ? |
sysconstraints. All indices are listed in sysindexes
Quote:
|
Do we have to see the name of the index in sysconstrainsts ? |
Quote:
|
(in idxname , or constrname) just for information, since i do not need this Thank you once again Arnaud |
#5
| |||
| |||
|
|
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 |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.