Re: Problem in retriving in column name using constraint name
voora srinivas wrote:
>
> Hello,
> I got a problem in retriving information from system catalog.
> I created the following table in the database
> CREATE TABLE Address
> (
> number INTEGER NOT NULL,
> name CHAR(35) NOT NULL,
> vorname CHAR(35) NOT NULL,
> children DECIMAL(22) NOT NULL,
> UNIQUE (number),
> UNIQUE (name)
> ) LOCK MODE ROW;
> and i delebarately entered two rows with same names . The sqlca structure
> has returned the following information
> result last sqlstat/stat type (sqlca.sqlcode): -268 dec
> informix error message (sqlca.sqlerrm): >>voora.u102_8<<
> esitmated nr of rows returned (sqlca.sqlerrd[0]): 1 dec
> serial value or ISAM err code (sqlca.sqlerrd[1]): 0 dec
> number or rows processed (sqlca.sqlerrd[2]): 0 dec
> estimated cost (sqlca.sqlerrd[3]): 1 dec
> offset of error in statement (sqlca.sqlerrd[4]): 73 dec
> rowid of last row processed (sqlca.sqlerrd[5]): 0 dec
> The system has assigned the names to the Unique constraints
> __________________________________
> Constraint Name Column Name
> __________________________________
> u102_7 number
> u102_8 name
> ___________________________________
> My problem is i want to retrive the column name(ie name) using Constraint Name(u102_8)
> using an SQL statement from system catalogs.
The sysconstraints records contain the constraint type and the name of
the index used to enforce the constraint if needed. The sysindexes
table contains the list of column numbers indexed. The syscolumns
table contains the column names by tabid and column number. The
sysconstraints table also contains tabid. So:
select c.colname
from syscolumns c, sysindexes i, sysconstraints t
where c.colno = ABS(i.part1)
and c.tabid = t.tabid
and i.idxname = t.idxname
and t.constrname = "ul102_8";
This, of course, will work for single column uniqueness constraints.
For multiple column unique keys you will have to join each of part[2-16]
to syscolumns or do this in a Stored Procedure or programming language
and fetch the column numbers from sysindexes INTO an array and then get
the corresponding column names in a loop.
Art S. Kagel