Informix

Expand all | Collapse all

what is related with?

  • 1.  what is related with?

    Posted Mon March 15, 2021 01:17 PM
    Hi,

    I am trying to find one query to identify all table names with connections for my foreign keys...

    Thanks in advance for any help,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------


  • 2.  RE: what is related with?

    Posted Mon March 15, 2021 01:42 PM
    Hmm, several options based on what you really need to know:

    If what tables are dependent on a given table, you can use myschema with the -F option:

    myschema -d art -t 'parent' -F -q
    CREATE TABLE "informix".parent (
           one SERIAL(1) NOT NULL
    ) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

    {
           Please review extent sizing and adjust to allow for growth.
    }

    REVOKE ALL ON "informix".parent FROM public AS "informix";
    CREATE UNIQUE INDEX "informix".p659_7249 ON "informix".parent (
           one ASC
    ) USING btree IN datadbs_1;


    GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON "informix".parent TO "public" AS "informix";

    ALTER TABLE "informix".parent ADD CONSTRAINT PRIMARY KEY (
           one
    ) ;

    ALTER TABLE "art".child ADD CONSTRAINT FOREIGN KEY (
           one
    ) REFERENCES parent (
           one
    ) ;

    ALTER TABLE "art".other_child ADD CONSTRAINT FOREIGN KEY (
           one
    ) REFERENCES parent (
           one
    ) CONSTRAINT "art".other_child_fk1;

    If you need to know every reference, you can:

    myschema -q -d art --constraint-file=fk /dev/null  

    The file (fk) will contain all of the primary keys followed by the unique keys followed by th foreign keys.

    If you really need a query telling you what tables are referring to which tables, then:

    SELECT st.tabname, st.owner, rt.tabname, rt.owner, sr.primary, sr.ptabid, 
                   sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner,
                   si.idxname, os.state, os2.state "
    FROM "informix".systables st, "informix".sysconstraints sc, 
                "informix".sysindexes si, "informix".sysreferences sr, 
                "informix".systables rt, "informix".sysconstraints rc,
                "informix".sysobjstate os, "informix".sysobjstate os2 
    WHERE st.tabid = sc.tabid 
      AND st.tabtype != 'Q' 
      AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*'
      AND rt.tabid = sr.ptabid 
      AND sc.constrid = sr.constrid 
      AND sc.tabid = si.tabid 
      AND sc.idxname = si.idxname 
      AND sc.constrtype = 'R'
      AND st.tabname MATCHES ?     -- Remove this line or replace ? with '*' for all tables, tabname for one
      AND os.tabid = st.tabid AND os.name = sc.constrname AND os.objtype = 'C' 
      AND os2.tabid = st.tabid AND os2.name = si.idxname AND os2.objtype = 'I' 
      AND sr.primary = rc.constrid
    ORDER BY si.tabid, sc.constrid ;




    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: what is related with?

    Posted Tue March 16, 2021 05:46 AM
    Thanks Art,

    That can help me a lot.

    Best regards,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------