referential constraints 관련 정보 (Gary Ben-Israel)

IIUG Insider #190에 실린 기사입니다.

Gary Ben-Israel 씨의 참조 제약조건 정보를 나열하는 쿼리문장이 소개되어 있습니다.

참고하셔서 사용하실 수 있겠네요.

In this section I will write about things that help me in my day to day work.

Most DBAs probably have their own way to perform these tasks which may be different than the way I do them. So, if you find an error or can think of a better way, please let me know. If not feel free to use these tips as is or modify them to fit your needs.

Today's topic is a view my developers use when they want to find which tables are referencing the table they are dealing with. Some SQL editors can display this information but even when they do it is not always intuitive or easy.

In DBACCESS for instance it takes you six steps. At least that's what it takes me. Needless to say my developers are not familiar with DBACCESS and are not logged into an Informix server.

Performing this with a simple select statement can be helpful. We are using the following view:

create view back_ref_view

(referenced_table, referenced_column, referencing_table, referencing_column,

 cascading_delete, constraint_name, back_ref_view_ik) as

SELECT a.tabname

       d.colname,

       g.tabname,

       i.colname,

       CASE

         WHEN e.delrule = "C" THEN

              "Yes"

         ELSE

              "No"

         END cascaing_delete,

       f.constrname,

       f.constrid

FROM  systables a, sysconstraints b, sysindexes c, syscolumns d,

      sysreferences e, sysconstraints f, systables g, sysindexes h,

      syscolumns i

WHERE b.tabid = a.tabid

  AND b.constrtype = "P"

  AND c.idxname = b.idxname

  AND d.tabid = c.tabid

  AND d.colno = c.part1

  AND e.primary = b.constrid

  AND f.constrid = e.constrid

  AND g.tabid = f.tabid

  AND h.idxname = f.idxname

  AND i.tabid = h.tabid

  AND i.colno = h.part1;

grant select on back_ref_view to "public";

Gary Ben-Israel

http://www.iiug.org/Insider/insider_apr16.php#W4M