Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    3

    Unanswered: How can I get all tables that have FK on a PK?

    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...
    Last edited by Gaston de Foix; 09-29-05 at 05:54.

  2. #2
    Join Date
    Sep 2002
    Posts
    102
    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.

  3. #3
    Join Date
    Sep 2005
    Posts
    3
    Tks for response.

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

  4. #4
    Join Date
    Sep 2002
    Posts
    102
    I can give you want you want but you got to tell me how many columns are in the primary key.

  5. #5
    Join Date
    Sep 2002
    Posts
    102
    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
    ;

  6. #6
    Join Date
    Sep 2005
    Posts
    3
    Thanks a lot man. It's working

    You are the best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •