Re: Problem with primary key
Hi Vinhson,
> has a primary key. Does anyone know how I can find the column name
> associated with that key?
>
> Any help or suggestion is very much appreciated.
I had to solve a similar problem, I had to provide a
table info for a delphi client. After trying out several things I found
the best way for me was to create a view from informix system tables.
With this view I also get the primary key information for each table of
the database.
--snip--
create view tabinfoview as
SELECT DISTINCT
s1.tabid, s2.tabname, s1.colno, s1.colname, s1.coltype, s5.type,
s5.default[1,30],s7.part1, s7.part2, s7.part3, s7.part4, s7.part5, s7.part6
FROM
"informix".syscolumns s1, "informix".systables s2,
"informix".sysconstraints s6,
"informix".sysindexes s7,
OUTER "informix".sysdefaults s5
WHERE
s2.tabid = s1.tabid AND
s2.tabid = s6.tabid AND
s7.tabid = s6.tabid AND
s7.idxname = s6.idxname AND
s7.tabid = s2.tabid AND
s5.tabid = s1.tabid AND
s5.colno = s1.colno AND
s6.constrtype = "P" AND
s2.tabtype = "T";
I not selected all pkparts because I have no primary key with more than
3 columns in the database.
--
best regards
Katrin
__________
_/_/_/_/_/ _/_/_/_/ _/_/_/_/ Katrin Goericke katrin@taz.de
_/ _/ _/ _/ die tageszeitung
_/ _/_/_/_/ _/ Germany
_/ _/ _/ _/ D-10969 Berlin Phone:
+49-30-2516092
_/ _/ _/ _/_/_/_/ Kochstr. 18 FAX :
+49-30-2510694