Hi

I'm new in Informix. I have a big problem.

I have table "person" with a Primary key "pk_idPers" on a field "idPers":
------------
| idPers |
------------
| name |
------------
| pname |
------------

I want all tables(and coresponding fields for foreign key) that have foreign keys on idPers?

Example: Let's say that I have 30 tables that have foreign keys to table "person". I have in table "person" a record with idPers=1.
I want to get all tables from those 30 that have in field (that is in foreign key) the value 1.

My job is when I delete a record from a table to get all tables that have foreign keys to Primary key of my table.

All I achived is to get all tables that have foreign keys but I can't get the fields on FK. If I can get the fields in a stored procedure I want execute at runtime a statement that will retrive exactly the tables that have the value 1 for field that is in FK.

select SYSTABLES.tabname, con2.constrname from SYSREFERENCES,
sysconstraints con1, sysconstraints con2, SYSTABLES where
con1.constrname='pk_idPers' and SYSREFERENCES.primary=con1.constrid and
SYSREFERENCES.constrid=con2.constrid and con2.tabid=SYSTABLES.tabid;

I hope you understand, and somebody can help me. Thanks in advance...

select e.tabname
from systables a,
sysconstraints b,
sysreferences c,
sysconstraints d,
systables e
where a.tabname='person' and
a.tabid=b.tabid and b.constrtype='P' and
b.constrid=c.primary and
b.tabid=c.ptabid and
c.constrid=d.constrid and
d.tabid=e.tabid
;


Let me know if this is what you're looking for.

Tks for response.

Yes and No. I want columns too. Your select display only tables

I can give you want you want but you got to tell me how many columns are in the primary key.

Here you go, assuming only 1 column key:

select e.tabname,g1.colname
from systables a,
sysconstraints b,
sysreferences c,
sysconstraints d,
systables e,
sysindexes f,
syscolumns g1
where a.tabname='person' and
a.tabid=b.tabid and b.constrtype='P' and
b.constrid=c.primary and
b.tabid=c.ptabid and
c.constrid=d.constrid and
d.tabid=e.tabid and
e.tabid=f.tabid and
f.idxname=d.idxname and
f.tabid=g1.tabid and abs(f.part1)=g1.colno
;

Thanks a lot man. It's working

You are the best.