Oninit cdi / Archive
Talk to an expert →

Help required quickly.....Showing Informix NOT NULL constraints

Posted in 2003

Topics: Stored Procedures & SPL, Platform-Specific Issues, Versions, Editions & End-of-Life

Reeves, Damion A — — source: IIUG Forums & Mailing Lists

Hi, We are running IDS 9.21.FC3X-1 on HP-UX 11.0. I run the following SQL against our production database to try and display Primary Keys, Indexes and NOT NULL constraints, but only the Primary Keys and associated Indexes are displayed. Does anyone know how to also show the NOT NULL constraints ? select a.tabname as TABLE_NAME, c.colname as COLUMN_NAME, b.constrname as CONSTRAINT_NAME, b.constrtype as CONSTRAINT_TYPE, b.idxname as INDEX_NAME from systables a, sysconstraints b, syscolumns c, sysindexes d where a.tabid=b.tabid and constrtype in ("P","N") and a.tabid=c.tabid and b.idxname = d.idxname and (d.part1=c.colno or d.part2=c.colno or d.part3=c.colno or d.part4=c.colno or d.part5=c.colno or d.part6=c.colno or d.part7=c.colno or d.part8=c.colno or d.part9=c.colno or d.part1=c.colno or d.part11=c.colno or d.part12=c.colno or d.part13=c.colno or d.part14=c.colno or d.part15=c.colno or d.part16=c.colno) and a.tabname not like "sys%" and a.tabid > 100 order by 1,2 Thanks ! Damion Reeves Informix Database Administrator EDS Australia Adelaide Solution Centre

↪ replying to Reeves, Damion A
Ravi Krishna — — source: IIUG Forums & Mailing Lists

Not null constraints are stored in column type itself (syscolumns.coltype). NOT NULL columns have coltype value 256 more than the value of coltype if it is not null. For e.g if null allowed CHAR field coltype is 1, then not null CHAR field coltype will be 257 and so on for each column type. Ravi ----- Original Message ----- From: "Reeves, Dam...." <damion.reeves@eds.com> To: <ids@iiug.org> Sent: October 16, 2003 19:29 Subject: Help required quickly.....Showing Informix NOT NULL constraints [2037] > Hi, > > > We are running IDS 9.21.FC3X-1 on HP-UX 11.0. > > I run the following SQL against our production database to try and display > Primary Keys, Indexes and NOT NULL constraints, but only the Primary Keys > and associated Indexes are displayed. > > Does anyone know how to also show the NOT NULL constraints ? > > > select > a.tabname as TABLE_NAME, > c.colname as COLUMN_NAME, > b.constrname as CONSTRAINT_NAME, > b.constrtype as CONSTRAINT_TYPE, > b.idxname as INDEX_NAME > from > systables a, > sysconstraints b, > syscolumns c, > sysindexes d > where > a.tabid=b.tabid > and constrtype in ("P","N") > and a.tabid=c.tabid > and b.idxname = d.idxname > and > (d.part1=c.colno or > d.part2=c.colno or > d.part3=c.colno or > d.part4=c.colno or > d.part5=c.colno or > d.part6=c.colno or > d.part7=c.colno or > d.part8=c.colno or > d.part9=c.colno or > d.part1=c.colno or > d.part11=c.colno or > d.part12=c.colno or > d.part13=c.colno or > d.part14=c.colno or > d.part15=c.colno or > d.part16=c.colno) > and a.tabname not like "sys%" > and a.tabid > 100 > order by 1,2 > > > > Thanks ! > > Damion Reeves > Informix Database Administrator > EDS Australia > Adelaide Solution Centre > > > >

↪ replying to Reeves, Damion A
Obnoxio The Clown — — source: IIUG Forums & Mailing Lists

dbschema -ss ? :o) -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco >From: "Reeves, Dam...." <damion.reeves@eds.com> >To: ids@iiug.org >Subject: Help required quickly.....Showing Informix NOT NULL constraints >[2037] Date: Thu, 16 Oct 2003 19:29:53 -0400 (EDT) > >Hi, > > >We are running IDS 9.21.FC3X-1 on HP-UX 11.0. > >I run the following SQL against our production database to try and display >Primary Keys, Indexes and NOT NULL constraints, but only the Primary Keys >and associated Indexes are displayed. > >Does anyone know how to also show the NOT NULL constraints ? > > >select > a.tabname as TABLE_NAME, > c.colname as COLUMN_NAME, > b.constrname as CONSTRAINT_NAME, > b.constrtype as CONSTRAINT_TYPE, > b.idxname as INDEX_NAME >from > systables a, > sysconstraints b, > syscolumns c, > sysindexes d >where > a.tabid=b.tabid > and constrtype in ("P","N") > and a.tabid=c.tabid > and b.idxname = d.idxname > and > (d.part1=c.colno or > d.part2=c.colno or > d.part3=c.colno or > d.part4=c.colno or > d.part5=c.colno or > d.part6=c.colno or > d.part7=c.colno or > d.part8=c.colno or > d.part9=c.colno or > d.part1=c.colno or > d.part11=c.colno or > d.part12=c.colno or > d.part13=c.colno or > d.part14=c.colno or > d.part15=c.colno or > d.part16=c.colno) > and a.tabname not like "sys%" > and a.tabid > 100 >order by 1,2 > > > >Thanks ! > >Damion Reeves >Informix Database Administrator >EDS Australia >Adelaide Solution Centre > > > _________________________________________________________________ Tired of 56k? Get a FREE BT Broadband connection http://www.msn.co.uk/specials/btbroadband

↪ replying to Reeves, Damion A
Jonathan Leffler — — source: IIUG Forums & Mailing Lists

Use: tabid >= 100 rather than tabid > 100. Since NOT NULL constraints are not associated with indexes, I would assume you'd have to revise your query quite considerably. I'm not sure that your query for primary keys is operational either - did it work for you? I note that it will *not* handle descending keys (which probably doesn't matter since you probably don't have any - but a general purpose tool would have to deal with negative column numbers in sysindexes). If you're going to do it 'all in one', you probably need two references to sysconstraints - the current one is OK for the indexes, and the second would be used for the not-null parts. Of course, all columns in a primary key are not null, so you could simply assume that! -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Reeves, Dam...."| | | <damion.reeves@ed| | | s.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/16/2003 04:29 | | | PM | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Help required quickly.....Showing Informix NOT NULL constraints [2037] | >------------------------------------------------------------------------------- --------------------------------------------------------------| Hi, We are running IDS 9.21.FC3X-1 on HP-UX 11.0. I run the following SQL against our production database to try and display Primary Keys, Indexes and NOT NULL constraints, but only the Primary Keys and associated Indexes are displayed. Does anyone know how to also show the NOT NULL constraints ? select a.tabname as TABLE_NAME, c.colname as COLUMN_NAME, b.constrname as CONSTRAINT_NAME, b.constrtype as CONSTRAINT_TYPE, b.idxname as INDEX_NAME from systables a, sysconstraints b, syscolumns c, sysindexes d where a.tabid=b.tabid and constrtype in ("P","N") and a.tabid=c.tabid and b.idxname = d.idxname and (d.part1=c.colno or d.part2=c.colno or d.part3=c.colno or d.part4=c.colno or d.part5=c.colno or d.part6=c.colno or d.part7=c.colno or d.part8=c.colno or d.part9=c.colno or d.part1=c.colno or d.part11=c.colno or d.part12=c.colno or d.part13=c.colno or d.part14=c.colno or d.part15=c.colno or d.part16=c.colno) and a.tabname not like "sys%" and a.tabid > 100 order by 1,2 Thanks ! Damion Reeves Informix Database Administrator EDS Australia Adelaide Solution Centre

Related threads

Glossary