How to find parent reference records in informix
I want to remove a person from my database:
remove from person where id is in (14)
but i have this exception
Key value for constraint (pk_person_id) is still being referenced.
I have no idea where else there is any record owned by this person because I have about 100 referenced tables. Is there a way to find these records in the informix?
PS: i cant drop constaint
+3
source to share
1 answer
you can try this, this will find all other tables where the foreign key matches the first key of the person table and has the value that exists in the 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
;
+4
source to share