Entity-relationship between tables
Hi, When I delete a record I would like to delete all of its associated records. Is there a handy tool somewhere that let me identity what the associated records are ? Thanks, Thanh
Are there two parts of your question -
1. Related to cascade delete
2. Tool for identifying dependencies.
Regd. point 1 - I am not 100% sure but probably you are right.
Regd. point 2 - Here is the beautiful script which was posted here only.
----------------------------------------------------------------------------
------------------------------------
#!/bin/ksh
########################################################################
#
# Quick script thrown together to find dependencies on a table.
#
# Usage:
# depend.sh <database> <table>
#
# This will list all tables which refer to this table either by view or
# by foreign key. It will also list any tables that this table depends
# on.
#
# Since there is a lot of excess white space in the output, I recommend
# that this be run as 'depend.sh database table | grep -v ^$'
#
#
########################################################################
find_par_view()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(b.tabname)
from sysdepend c, systables a, systables b
where btabid=a.tabid
and dtabid=b.tabid
and b.tabname = "$2";
EOF
}
find_child_view()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(b.tabname)
from sysdepend c, systables a, systables b
where btabid=a.tabid
and dtabid=b.tabid
and a.tabname = "$2";
EOF
}
find_par_fk()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(d.tabname) || ' ' || trim(constrname)
from systables a, sysconstraints b, sysreferences c, systables d
where constrtype='R'
and a.tabid=b.tabid
and b.constrid=c.constrid
and c.ptabid=d.tabid
and a.tabname = "$2";
EOF
}
find_child_fk()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(d.tabname) || ' ' || trim(constrname)
from systables a, sysconstraints b, sysreferences c, systables d
where constrtype='R'
and a.tabid=b.tabid
and b.constrid=c.constrid
and c.ptabid=d.tabid
and d.tabname = "$2";
EOF
}
rec_pfk()
{
idnt="$idnt-"
find_par_fk $1 $2 | grep -v "^$" |
while read junk
do
echo $idnt "Parent of FK: $junk"
tab=`echo $junk | cut -d" " -f1 `
rec_pfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}
rec_cfk()
{
idnt="$idnt-"
find_child_fk $1 $2 | grep -v "^$" |
while read junk
do
echo $idnt "Child of FK: $junk"
tab=`echo $junk | cut -d" " -f1 `
rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}
rec_pvw()
{
idnt="$idnt-"
find_par_view $1 $2 | grep -v "^$" |
while read tab
do
echo $idnt "Parent View : $tab"
rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}
rec_cvw()
{
idnt="$idnt-"
find_child_view $1 $2 | grep -v "^$" |
while read tab
do
echo $idnt "Child of View : $tab"
rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}
echo "
Database: $1
"
idnt=""
echo "Searching for tables that ($2) has an FK to:"
rec_pfk $1 $2
idnt=""
echo "Searching for tables that refer to ($2) with an FK:"
rec_cfk $1 $2
idnt=""
echo "Searching for Parent tables for potential view ($2):"
rec_pvw $1 $2
idnt=""
echo "Searching for Child tables for table ($2):"
rec_cvw $1 $2
----------------------------------------------------------------------------
------------------------------------
"Ma, Thanh(IndSys, GE Interlogix)" <Thanh.Ma@ge.com> wrote in message
news:bbg5mt$461$1@terabinaries.xmission.com...
>
> Hi,
>
> When I delete a record I would like to delete all of its associated
records.
> Is there a handy tool somewhere that let me identity what the associated
records are ?
>
> Thanks,
> Thanh
>