CODEWITHSUNDEEP
sqlinformix
hudi
hudi

Reputation: 16555

How to find referenced records of parent in informix

I want to delete person in my database:

delete from person where id in (14)

but I have this exception 

Key value for constraint (pk_person_id) is still being referenced.

I have no idea where there are still some record which belong to this person because I have about 100 referenced tables. Is there a way ho to find this records in informix ?

PS: I cant drop constaint

Upvotes: 4

Views: 6381

Answers (1)

radar
radar

Reputation: 13425

you can try this ,this will find all other tables where foreign key is matching with primary key of person table and has a value that exists in person table for that column.

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
;

Upvotes: 10

Related Questions